MacLochlainns Weblog

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.

1. Conditionally drop the test table.

   /* Suppress warnings from the log file. */ SET client_min_messages = 'error';   /* Conditionally drop table. */ DROP TABLE IF EXISTS test;

   /* Suppress warnings from the log file. */ SET client_min_messages = 'error'; /* Conditionally drop table. */ DROP TABLE IF EXISTS test;

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

   /* 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));

3. 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');

   /* 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');

4. Create a five second delay.

   /* Sleep for 5 seconds. */ DO $$ BEGIN PERFORM pg_sleep(5); END; $$;

   /* Sleep for 5 seconds. */ DO $$ BEGIN PERFORM pg_sleep(5); END; $$;

5. Use the INSERT statement with a DO UPDATE clause that increments the updated column of the test 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;

   /* 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;

6. Use the INSERT statement with a DO 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;

   /* 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;

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

   /* 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)

   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.