MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘Upsert’ Category

PostgreSQL Upsert Intro

with one comment

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;

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

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

  4. Create a five second delay.

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

  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;

  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;

    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.

Written by maclochlainn

November 24th, 2019 at 7:26 pm