PostgreSQL INSERT, UPDATE, DELETE - PostgreSQL Tutorial

INSERT

SQL

INSERT INTO books (title, author, price)
VALUES ('Palpasa Cafe', 'Narayan Wagle', 480);

-- several rows at once (much faster than separate statements)
INSERT INTO books (title, author, price) VALUES
    ('Muna Madan', 'Laxmi Prasad Devkota', 250),
    ('Karnali Blues', 'Buddhisagar', 550);

Always list the columns. INSERT INTO books VALUES (...) breaks the moment someone adds or reorders a column.

RETURNING

A Postgres feature worth knowing — get generated values back without a second query:

SQL

INSERT INTO books (title, price)
VALUES ('New Book', 300)
RETURNING id, created_at;

UPDATE

SQL

UPDATE books SET price = 520 WHERE id = 1;

UPDATE books
SET price = price * 1.10,
    updated_at = now()
WHERE author = 'Buddhisagar';

A missing WHERE updates every row in the table. Before running an UPDATE or DELETE by hand, run it as a SELECT first:

SQL

SELECT * FROM books WHERE author = 'Buddhisagar';   -- check what matches
UPDATE books SET price = price * 1.10 WHERE author = 'Buddhisagar';

DELETE

SQL

DELETE FROM books WHERE id = 5;
DELETE FROM books WHERE price IS NULL;

DELETE FROM books;         -- every row!
TRUNCATE books;             -- same, but much faster and resets sequences

TRUNCATE cannot be filtered and doesn't fire row triggers — use it for clearing out test data, not for ordinary deletes.

UPSERT — INSERT ... ON CONFLICT

Insert, or update if the row already exists:

SQL

INSERT INTO books (isbn, title, price)
VALUES ('9789937812345', 'Palpasa Cafe', 480)
ON CONFLICT (isbn) DO UPDATE
SET title = EXCLUDED.title,
    price = EXCLUDED.price;

EXCLUDED refers to the row you tried to insert. To ignore duplicates instead:

SQL

INSERT INTO books (isbn, title) VALUES ('...', '...')
ON CONFLICT (isbn) DO NOTHING;

The conflict target needs a unique constraint or unique index on it.

Copying rows between tables

SQL

INSERT INTO archived_books (title, author, price)
SELECT title, author, price FROM books WHERE published < '2000-01-01';