PostgreSQL Upsert Intro
Oracle and SQL Server use the MERGE
statement, MySQL uses the REPLACE INTO
statement or ON DUPLICATE KEY
, but PostgreSQL uses an upsert. The upsert isn’t a statement per se. It is like MySQL’s INSERT
statement with the ON DUPLICATE KEY
clause. PostgreSQL uses an ON CONFLICT
clause in the INSERT
statement and there anonymous block without the $$
delimiters.
The general behaviors of upserts is covered in the PostgreSQL Tutorial. It has the following prototype:
INSERT INTO TABLE_NAME(column_list) VALUES(value_list) ON CONFLICT target action; |
The target can be a column name, an ON CONSTRAINT
constraint name, or a WHERE
predicate, while the action can be DO NOTHING
(or ignore) or a DO UPDATE
statement. I wrote the following example to show how to leverage a unique constraint with a DO NOTHING
and DO UPDATE
behavior.
My example conditionally drops a table, creates a table with a unique constraint, inserts a few rows, updates with a DO UPDATE
clause, updates with DO NOTHING
clause, and queries the results with a bit of formatting.
- Conditionally drop the
test
table./* Suppress warnings from the log file. */ SET client_min_messages = 'error'; /* Conditionally drop table. */ DROP TABLE IF EXISTS test;
- Create the
test
table./* Create a test table. */ CREATE TABLE test ( test_id SERIAL , first_name VARCHAR(20) , middle_name VARCHAR(20) , last_name VARCHAR(20) , updated INTEGER DEFAULT 0 , update_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP , CONSTRAINT test_uq_key UNIQUE (first_name,middle_name,last_name));
- Insert six rows into the
test
table./* Insert six rows. */ INSERT INTO test ( first_name, middle_name, last_name ) VALUES ('Harry','James','Potter') ,('Ginerva','Molly','Potter') ,('Lily','Luna','Potter') ,('Albus','Severus','Potter') ,('James',NULL,'Potter') ,('Lily',NULL,'Potter');
- Create a five second delay.
/* Sleep for 5 seconds. */ DO $$ BEGIN PERFORM pg_sleep(5); END; $$;
- Use the
INSERT
statement with aDO UPDATE
clause that increments theupdated
column of thetest
table./* Upsert on unique key constraint conflict. */ INSERT INTO test ( first_name , middle_name , last_name ) VALUES ('Harry' ,'James' ,'Potter') ON CONFLICT ON CONSTRAINT test_uq_key DO UPDATE SET updated = excluded.updated + 1 , update_time = CURRENT_TIMESTAMP;
- Use the
INSERT
statement with aDO NOTHING
clause./* Upsert on unique key constraint ignore update. */ INSERT INTO test ( first_name , middle_name , last_name ) VALUES ('Harry' ,'James' ,'Potter') ON CONFLICT ON CONSTRAINT test_uq_key DO NOTHING;
- Query the
test
table./* Formatted query to demonstrate result of UPSERT statement. */ SELECT test_id , last_name || ', ' || CASE WHEN middle_name IS NOT NULL THEN first_name || ' ' || middle_name ELSE first_name END AS full_name , updated , date_trunc('second',update_time AT TIME ZONE 'MST') AS "timestamp" FROM test ORDER BY last_name , first_name , CASE WHEN middle_name IS NOT NULL THEN middle_name ELSE 'A' END;
Display results:
test_id | full_name | updated | timestamp ---------+-----------------------+---------+--------------------- 4 | Potter, Albus Severus | 0 | 2019-11-24 19:23:10 2 | Potter, Ginerva Molly | 0 | 2019-11-24 19:23:10 1 | Potter, Harry James | 1 | 2019-11-24 19:23:15 5 | Potter, James | 0 | 2019-11-24 19:23:10 6 | Potter, Lily | 0 | 2019-11-24 19:23:10 3 | Potter, Lily Luna | 0 | 2019-11-24 19:23:10 (6 rows)
As always, I hope this helps those looking for clear examples to solve problems.