wikis / PostgreSQL / wiki / concepts / dml.md view as markdown report a mistake

Data Manipulation: INSERT, UPDATE, DELETE

Data manipulation language (DML) fills and changes table contents. Each statement runs inside a transaction (see transactions and mvcc).

INSERT

INSERT adds rows. Values may be given positionally or with an explicit column list; omitted columns take their defaults:

INSERT INTO products VALUES (1, 'Cheese', 9.99);
INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', 9.99);
INSERT INTO products (product_no, name) VALUES (1, 'Cheese');
INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', DEFAULT);
INSERT INTO products DEFAULT VALUES;

Insert multiple rows, or the result of a query:

INSERT INTO products (product_no, name, price) VALUES
    (1, 'Cheese', 9.99),
    (2, 'Bread', 1.99),
    (3, 'Milk', 2.99);

INSERT INTO products (product_no, name, price)
  SELECT product_no, name, price FROM new_products WHERE release_date = 'today';

For bulk loads, COPY is faster than INSERT.

UPDATE

UPDATE ... SET ... WHERE changes columns of matching rows; the new value can be any expression and may reference the existing value. Omitting WHERE updates every row.

UPDATE products SET price = 10 WHERE price = 5;
UPDATE products SET price = price * 1.10;
UPDATE mytable SET a = 5, b = 3, c = 1 WHERE a > 0;

DELETE

DELETE FROM ... WHERE removes matching rows; without WHERE it removes them all.

DELETE FROM products WHERE price = 10;
DELETE FROM products;   -- removes every row

Upsert: INSERT ... ON CONFLICT

An ON CONFLICT clause handles uniqueness conflicts instead of erroring: ON CONFLICT DO NOTHING skips conflicting rows, while ON CONFLICT (...) DO UPDATE performs an "upsert" (the recommended alternative to a manual UPDATE-then-INSERT loop). Under Read Committed, each proposed row is guaranteed to either insert or update.

RETURNING

INSERT, UPDATE, and DELETE accept a RETURNING clause (same form as a SELECT list, or RETURNING *) to return modified-row data without a separate query — handy for generated keys:

INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING id;
UPDATE products SET price = price * 1.10 WHERE price <= 99.99 RETURNING name, price AS new_price;
DELETE FROM products WHERE obsoletion_date = 'today' RETURNING *;

RETURNING can also reference old. and new. values, and works for data-modifying statements inside a WITH query (see queries and joins).