PostgreSQL INSERT, UPDATE, DELETE and upsert

These commands change data. INSERT adds rows (one, many, or the result of a query). UPDATE changes columns in rows matching a WHERE. DELETE removes rows matching a WHERE. An UPDATE or DELETE without WHERE affects every row; run the matching SELECT first if you’re unsure.

PostgreSQL extras that you’ll use constantly:

RETURNING gives back the inserted/updated/deleted rows, such as a new id, without a second query.
INSERT ... ON CONFLICT DO NOTHING / DO UPDATE is an “upsert”: insert, or update if the key already exists. EXCLUDED refers to the row you tried to insert.
UPDATE ... FROM and DELETE ... USING let you use other tables in the condition.
TRUNCATE empties a table instantly.

Tip: wrap experiments in BEGIN; ... ROLLBACK; to see the effect and then undo it.

Syntax

INSERT INTO t (a, b) VALUES (1, 'x'), (2, 'y') RETURNING id;
UPDATE t SET a = a + 1 WHERE condition;
DELETE FROM t WHERE condition;
INSERT ... ON CONFLICT (key) DO UPDATE SET col = EXCLUDED.col;

Examples

Edit the code and run it. Output is printed the way psql prints it.

17.1INSERT with RETURNING

INSERT INTO customers (name, email, city, country, tier)
VALUES ('Tara Menon', 'tara@example.in', 'Kochi', 'India', 'pro'),
       ('Ben Carter', 'ben@example.com', 'Austin', 'USA', DEFAULT)
RETURNING id, name, tier, signup_date;

Two rows in one statement. DEFAULT uses the column default (free). RETURNING shows the generated ids and defaults. Run it twice and the UNIQUE email rule stops you.

17.2INSERT ... SELECT

DROP TABLE IF EXISTS vip_customers;
CREATE TABLE vip_customers (customer_id integer PRIMARY KEY, name text, added_on date DEFAULT current_date);

INSERT INTO vip_customers (customer_id, name)
SELECT id, name FROM customers WHERE tier = 'enterprise';

SELECT * FROM vip_customers;

Instead of VALUES, the rows come from a query. The column count and types must match.

17.3UPDATE safely

BEGIN;

UPDATE products
SET price = round(price * 1.10, 2),
    stock = stock + 10
WHERE category = 'Office'
RETURNING id, name, price, stock;

ROLLBACK;  -- undo, so the shop data stays the same

Two columns change in one pass; the right-hand side sees the old values. RETURNING shows the new values, and ROLLBACK throws the change away.

17.4UPDATE ... FROM another table

BEGIN;
UPDATE customers c
SET tier = 'pro'
FROM (
  SELECT o.customer_id
  FROM orders o JOIN order_items oi ON oi.order_id = o.id
  GROUP BY o.customer_id
  HAVING sum(oi.quantity * oi.unit_price) > 150
) big
WHERE c.id = big.customer_id AND c.tier = 'free'
RETURNING c.name, c.tier;
ROLLBACK;

Upgrades free customers who spent more than 150. The FROM clause adds another table (here a derived table) to match against.

17.5DELETE and DELETE ... USING

BEGIN;
DELETE FROM orders WHERE status = 'cancelled' RETURNING id, customer_id;
SELECT count(*) AS items_left_for_cancelled FROM order_items WHERE order_id IN (6, 14);
ROLLBACK;

BEGIN;
DELETE FROM order_items oi
USING products p
WHERE p.id = oi.product_id AND p.category = 'Audio'
RETURNING oi.order_id, p.name;
ROLLBACK;

Deleting the cancelled orders also removed their items, because order_items.order_id is defined with ON DELETE CASCADE. The second statement deletes items by joining to products with USING.

17.6UPSERT with ON CONFLICT

DROP TABLE IF EXISTS page_views;
CREATE TABLE page_views (page text PRIMARY KEY, views integer NOT NULL);

INSERT INTO page_views VALUES ('/home', 1)
ON CONFLICT (page) DO UPDATE SET views = page_views.views + EXCLUDED.views;

INSERT INTO page_views VALUES ('/home', 1), ('/pricing', 1)
ON CONFLICT (page) DO UPDATE SET views = page_views.views + EXCLUDED.views;

INSERT INTO page_views VALUES ('/pricing', 99)
ON CONFLICT DO NOTHING;

SELECT * FROM page_views ORDER BY page;

The first insert creates /home. The second finds /home already there and adds to it instead, while inserting /pricing. DO NOTHING silently skips duplicates. This pattern replaces “check then insert or update” and is safe under concurrency.

Exercises

Write a query and check it. Any query that returns the right rows passes.

Exercise 17.1

Insert a product named Gaming Mouse in category Electronics priced 49.99 with stock 40.

Hint

INSERT INTO products (name, category, price, stock) VALUES (...)

Solution

INSERT INTO products (name, category, price, stock) VALUES ('Gaming Mouse', 'Electronics', 49.99, 40);

Exercise 17.2

Increase the price of every product in the Office category by 10%, rounded to 2 decimals.

Hint

UPDATE products SET price = round(price * 1.1, 2) WHERE ...

Solution

UPDATE products SET price = round(price * 1.1, 2) WHERE category = 'Office';

Exercise 17.3

Mark every pending order as cancelled if it has no sales rep (employee_id is NULL).

Hint

Two conditions in WHERE.

Solution

UPDATE orders SET status = 'cancelled' WHERE status = 'pending' AND employee_id IS NULL;