Upsert in PostgreSQL

Insert a row, or update it if it already exists, in one statement.

SNP 094 #591 Filed 5 months ago sql postgres

sql

INSERT INTO settings (user_id, key, value)
VALUES (42, 'theme', 'dark')
ON CONFLICT (user_id, key)
DO UPDATE SET value = EXCLUDED.value, updated_at = now();

The columns after ON CONFLICT need a unique constraint or index. EXCLUDED is the row you tried to insert.

To insert only if it isn't there, and otherwise do nothing:

sql

INSERT INTO tags (name) VALUES ('webdev')
ON CONFLICT (name) DO NOTHING;

MySQL and MariaDB spell it INSERT ... ON DUPLICATE KEY UPDATE value = VALUES(value), and SQLite supports the PostgreSQL syntax since 3.24.