SQL Server users are probably familiar with the upsert functionality provided from the MERGE statement. MERGE is part of the SQL standard, but isn’t implemented in Postgres. However, a coworker showed me how to implement the same functionality in Postgres. Starting with version 9.5, Postgres provides an ON CONFLICT option as part of an INSERT statement. This allows you to attempt an insert. If the specified value already exists, you have the option to either ignore the insert or to update the existing record.
Some sample data:
DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table(
test_id int NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
display_name varchar(10) NOT NULL UNIQUE,
sort_order int NOT NULL
);
INSERT INTO test_table(display_name, sort_order) VALUES
('Record 1', 1),
('Record 2', 2),
('Record 3', 3);
We’ll try to reinsert a value that already exists. We’ll tell the upsert to use the display_name value to check for duplicates. If the value already exists, DO NOTHING will tell the query to not try any further actions with that record.
INSERT INTO test_table(display_name, sort_order) VALUES
('Record 1', 1)
ON CONFLICT (display_name)
DO NOTHING;
Notice that when we created the table that we have a unique constraint defined for display_name. Otherwise, we’ll get an error:
“– ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification”
The data at this point:
| test_id | display_name | sort_order |
|---|---|---|
| 1 | Record 1 | 1 |
| 2 | Record 2 | 2 |
| 3 | Record 3 | 3 |
For the next insert, we’ll include a new display_name (Record 4) and change the sort_order values for 1 and 2.
If the records already exist, we’ll update any differences in the sort order value.
INSERT INTO test_table(display_name, sort_order) VALUES
('Record 1', 2),
('Record 2', 1),
('Record 3', 3),
('Record 4', 4)
ON CONFLICT (display_name)
DO UPDATE
SET sort_order = EXCLUDED.sort_order
WHERE test_table.sort_order <> EXCLUDED.sort_order;
The data at this point:
SELECT * FROM test_table ORDER BY sort_order;
| test_id | display_name | sort_order |
|---|---|---|
| 2 | Record 2 | 1 |
| 1 | Record 1 | 2 |
| 3 | Record 3 | 3 |
| 8 | Record 4 | 4 |
Now for a downside. You’ve probably noticed that the identity values of test_id have a gap from 3 to 8. So when we attempt an insert and there’s a conflict, a sequence value is still used up.
UPDATE – April 1, 2022:
Postgres – MERGE
I just saw where Postgres is adding the MERGE statement to an upcoming version, maybe sometime in 2022.
Links:
Postgres INSERT
SQL MERGE is quite distinct from UPSERT
Hidden dangers of duplicate key violations in PostgreSQL and how to avoid them
SQL Rob 