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:


CREATE TABLE test_table(
	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)

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)
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.

Postgres INSERT

SQL MERGE is quite distinct from UPSERT

Postgres Wiki: UPSERT

Hidden dangers of duplicate key violations in PostgreSQL and how to avoid them