Upsert in PostgreSQL with ON CONFLICT
Combine inserts and updates in one statement for data that may already exist.
You are reading a translated version.
Possible duplicate insert problems
When storing data from an outside source, the same row can be sent more than once. Performing SELECT to check the presence of data before INSERT susceptible to race condition when two processes run simultaneously.
ON CONFLICT DO UPDATE
CREATE TABLE statistik_artikel (
artikel_id BIGINT PRIMARY KEY,
jumlah_dilihat BIGINT NOT NULL DEFAULT 0
);
INSERT INTO statistik_artikel (artikel_id, jumlah_dilihat)
VALUES (42, 1)
ON CONFLICT (artikel_id)
DO UPDATE SET jumlah_dilihat = statistik_artikel.jumlah_dilihat + 1;
The above statement adds a new line if artikel_id does not yet exist, or add counters if one already exists, all in one atomic operation.
ON CONFLICT DO NOTHING
If you just want to ignore existing rows without changing anything, use DO NOTHING.
INSERT INTO email_terdaftar (email)
VALUES ('user@contoh.test')
ON CONFLICT (email) DO NOTHING;
Conditions for using ON CONFLICT
Fields mentioned in ON CONFLICT must have UNIQUE constraint or become the primary key. Without it, PostgreSQL does not know which conditions are considered conflict.
Distinguishing from MERGE
PostgreSQL version 15 and above also supports statements MERGE which is more flexible for insert, update, and delete cases at the same time based on the results of the join. ON CONFLICT keep it more concise for simple upsert cases based on one unique constraint.
practice;
Change the example stats above to also save the last seen time, and make sure the column is updated when a conflict occurs.