Data Manipulation Language

DML is a subset of SQL used for adding, updating, and deleting data within database tables.

• SELECT used to query data from the database

• INSERT used to insert new data into a table

• UPDATE used to modify existing data

• DELETE used to remove data from a table

Creating new records

When a table is created, it contains no data. Inserting data will populate a table with values. Data is inserted one row at a time. You can also insert multiple rows with a single command. But it impossible to insert something that is not a complete row.

To create a new row use INSERT command.

The data values are listed in the order in which the columns appear in the table, separated by commas. This syntax has the drawback that you need to know the order of the columns in the table. To avoid this you can also list the columns explicitly. If you don’t have values for all the columns, you can omit some of them. In that case, the columns will be filled with their default values.

Create publisher table

CREATE TABLE publisher
(
    id      INTEGER PRIMARY KEY,
    name    VARCHAR(128) NOT NULL CHECK (LENGTH(name) > 0),
    website VARCHAR(255) UNIQUE,
    email   VARCHAR(255),
    phone   VARCHAR(32) UNIQUE
);

COMMENT
    ON TABLE publisher IS 'publisher registered within a library db';
COMMENT
    ON COLUMN publisher.name IS 'non-zero length publisher name';

ALTER TABLE publisher
    OWNER TO libms;

Insert data to publisher table

INSERT INTO "publisher" ("id", "name", "website", "email", "phone")
VALUES (1, 'Zoonoodle', 'https://sfgate.com', 'bhaile0@blogtalkradio.com', '+55 (465) 224-8652');
INSERT INTO publisher (id, name, website, email, phone)
VALUES (2, 'Brainlounge', 'http://php.net', 'bfindlow1@paginegialle.it', '+389 (482) 470-2463');
INSERT INTO publisher
VALUES (3, 'Tanoodle', 'http://dyndns.org', 'cfleisch2@scribd.com', '+7 (852) 867-5041');
/*
 * queries above demonstrate various ways to *insert* data
 */

You can insert multiple rows in a single command.

Create author table

CREATE TABLE author
(
    id         SERIAL PRIMARY KEY,
    first_name VARCHAR(128) NOT NULL,
    last_name  VARCHAR(128) NOT NULL,
    country    VARCHAR(255),
    dob        DATE CHECK (dob < NOW() - INTERVAL '10 years'),
    CHECK (LENGTH(first_name) > 0),
    CHECK (LENGTH(last_name) > 0)
);

COMMENT
    ON TABLE author IS 'authors registered in the library db';
COMMENT
    ON COLUMN author.first_name IS 'non-zero length author''s first name';
COMMENT
    ON COLUMN author.last_name IS 'non-zero length author''s last name';
COMMENT
    ON COLUMN author.dob IS 'date of birth, at least 10 years old';

ALTER TABLE author
    OWNER TO libms;

Inserting multiple values at a time

INSERT INTO publisher
VALUES (18, 'Flipopia', 'http://dion.ne.jp', 'fmidsonh@cyberchimps.com', '+351 (187) 901-9626');
INSERT INTO author (first_name, last_name, country, dob)
VALUES ('Letta', 'Casbolt', 'Poland', '1947-04-18'),
       ('Robbyn', 'Attwoul', 'Poland', '1954-10-17'),
       ('Hesther', 'Kisby', 'Ukraine', '1941-07-21'),
       ('Gav', 'Jewett', 'Czech Republic', '1988-02-05'),
       ('Jorrie', 'Klehyn', 'United States', '1941-08-07'),
       ('Genevieve', 'Ollington', 'United States', '1921-08-27'),
       ('Carrissa', 'Arrandale', 'United Kingdom', '1982-08-20'),
       ('Josepha', 'Dominichelli', 'Poland', '1976-12-03'),
       ('Montague', 'Duerden', 'Poland', '2003-11-09');

Note

There is a reference to the “publisher” table present below. Think of it like placing the publisher information directly into “book” table for now.

Create book table

CREATE TABLE book
(
    id               SERIAL PRIMARY KEY,
    title            VARCHAR(255) NOT NULL,
    synopsis         TEXT,
    isbn             VARCHAR(16),
    publisher_id     INTEGER      NOT NULL REFERENCES publisher ON DELETE RESTRICT,
    publication_date DATE,
    language         VARCHAR(64),
    page_count       INTEGER,
    keywords         TEXT
);

COMMENT
    ON TABLE book IS 'available books';
COMMENT
    ON COLUMN book.title IS 'non-zero length book''s title';
COMMENT
    ON COLUMN book.keywords IS 'whitespace separated keywords';

ALTER TABLE book
    ADD CONSTRAINT check_title_length CHECK (LENGTH(title) > 0);

DROP TYPE IF EXISTS book_genre;
CREATE TYPE book_genre AS ENUM (
    'Adventure',
    'Biography',
    'Comedy',
    'Crime',
    'Drama',
    'Fantasy',
    'Historical Fiction',
    'Horror',
    'Mystery',
    'Poetry',
    'Romance',
    'Science Fiction',
    'Self-Help',
    'Thriller',
    'Young Adult'
    );

COMMENT
    ON TYPE book_genre IS 'registered book genres enumeration';

ALTER TABLE book
    ADD COLUMN genre book_genre;

ALTER TABLE book
    OWNER TO libms;

Inserting records from file

-- require superuser access or `pg_read_server_files` role priveleges
COPY book (id, title, synopsis, isbn, publisher_id, publication_date, genre, language, page_count,
           keywords) FROM '/var/lib/postgresql/assets/book.csv' DELIMITER ',' CSV HEADER;
-- update id sequence value
SELECT SETVAL('book_id_seq', (SELECT MAX(id) FROM book));

Modifying records

The modification of data that is already in the database is referred as updating. To update the existing rows, use the UPDATE command. This requires three pieces of information:

1. The name of the table and column(s) to update

2. The new value of the column(s)

3. Which row(s) to update

UPDATE book SET language = 'uk' WHERE publisher = 3;

This might cause zero, one, or many rows to be updated. The update syntax is pretty straightforward. First the key word UPDATE followed by the table name. Next is the key word SET followed by the column name, an equal sign, and the new column value. The new column value can be any scalar expression, not just a constant. More than one column can be updated at a time by listing more than one assignment in the SET clause.

Important

WHERE clause is optional, but it limits the rows affected by UPDATE command. If there is no limitations all the records within a table will be updated with new values.

UPDATE book SET language = 'uk';

Removing data

So far we have explained how to add data to tables and how to change data. What remains to discuss how to remove data that is no longer needed. Just as adding data is only possible in whole rows, you can only remove entire rows from a table.

You use DELETE command to remove rows; the syntax is very similar to the UPDATE command.

DELETE FROM book WHERE publisher = 3;

If you simply write:

DELETE FROM book;

then all rows in the table will be deleted!

Returning data from modified rows

Sometimes it is useful to obtain data from modified rows while they are being manipulated. The INSERT, UPDATE and DELETE commands all have an optional RETURNING clause that supports this. Use of RETURNING avoids performing an extra database query to collect the data, and is especially valuable when it would otherwise be difficult to identify the modified rows reliably.

INSERT INTO author (first_name, last_name, country, birthdate)
VALUES ('Wendye', 'Rowbotham', 'Poland', '1932-12-16'),
       ('Grannie', 'Kidner', 'United States', '1940-02-21'),
       ('Godart', 'Van Driel', 'United Kingdom', '1980-01-02'),
       ('Meara', 'Meenehan', 'United States', '1994-12-13')
RETURNING id;

For example the query above provides the identifiers to new rows.