Upsert & RETURNING

Two modern features make data changes simpler. An upsert inserts a row, or updates it if it already exists, in a single statement — no "check first, then decide" logic. RETURNING hands back the rows a statement changed, so you don't need a follow-up query.

The problem

INSERT INTO departments (id, name, location) VALUES (4, 'Support', 'Cork');

Error: UNIQUE constraint failed: departments.id

Department 4 exists already, so the insert fails. Checking with a SELECT first and then choosing INSERT or UPDATE works, but two users doing it at the same time can still collide.

INSERT … ON CONFLICT DO UPDATE

INSERT INTO departments (id, name, location)
VALUES (4, 'Support', 'Cork')
ON CONFLICT (id) DO UPDATE SET location = excluded.location;

SELECT * FROM departments WHERE id = 4;

If inserting would violate the unique key named in ON CONFLICT (…), the database updates the existing row instead. excluded refers to the row you tried to insert. This syntax is used by PostgreSQL and SQLite.

Upserting counters

CREATE TABLE product_views (
  product_id INTEGER PRIMARY KEY,
  views INTEGER NOT NULL
);

INSERT INTO product_views VALUES (4, 1) ON CONFLICT (product_id) DO UPDATE SET views = views + 1;
INSERT INTO product_views VALUES (4, 1) ON CONFLICT (product_id) DO UPDATE SET views = views + 1;
INSERT INTO product_views VALUES (7, 1) ON CONFLICT (product_id) DO UPDATE SET views = views + 1;

SELECT * FROM product_views;

The first view of a product inserts a row; later views increment it.

ON CONFLICT DO NOTHING

INSERT INTO customers (id, name, email, country, signup_date)
VALUES (1, 'Ana Pereira', 'ana.pereira@example.com', 'Portugal', '2024-02-11')
ON CONFLICT DO NOTHING;

SELECT COUNT(*) AS customers FROM customers;

Silently skips rows that already exist — useful when importing data that may overlap with what's already there.

Upserts in other databases

INSERT INTO departments (id, name, location)
VALUES (4, 'Support', 'Cork')
ON DUPLICATE KEY UPDATE location = VALUES(location);

RETURNING

INSERT INTO products (name, category, price, stock)
VALUES ('Reading Light', 'Home', 27.50, 20)
RETURNING id, name;

RETURNING gives back columns from the affected rows — here the newly generated id, without a second query. It works with UPDATE and DELETE too:

UPDATE products
SET price = round(price * 0.8, 2)
WHERE category = 'Toys'
RETURNING name, price AS sale_price;

DELETE FROM order_items
WHERE order_id = 5
RETURNING order_id, product_id, quantity;

RETURNING is supported by PostgreSQL, SQLite (3.35+), MariaDB and Oracle 23ai; SQL Server's equivalent is the OUTPUT clause.

Try it yourself

Record stock deliveries: add 10 to existing products and create rows for new ones, returning the results.