SQLDailyDrills

ON CONFLICT and RETURNING

Insert a row or update the existing one (upsert), and get back the rows that changed.

Syntax

INSERT INTO table VALUES (…) ON CONFLICT (key) DO UPDATE SET column = EXCLUDED.column;
INSERT … ON CONFLICT DO NOTHING;
UPDATE … RETURNING column_list;
  • EXCLUDED holds the row you tried to insert.
  • Other databases: MySQL ON DUPLICATE KEY UPDATE, SQL Server and Oracle MERGE.

Example

SELECT product_id, name FROM products WHERE product_id = 3;

Product 3 exists, so an upsert with key 3 would update it instead of failing.

Tables used: products. Try it in a lesson below.

Common mistakes

  • ON CONFLICT needs a unique constraint or primary key on the columns you name.
  • RETURNING is PostgreSQL (and SQLite, MariaDB); SQL Server uses OUTPUT.

Practise it