Hologres:INSERT ON CONFLICT(UPSERT)

Scenarios

The INSERT ON CONFLICT statement is suitable for scenarios in which data is written by executing SQL statements.

Syntax

INSERT INTO <table_name> [ AS <alias> ] [ ( <column_name> [, ...] ) ]
    {  VALUES ( { <expression> } [, ...] ) [, ...] | <query> }
    [ ON CONFLICT [ conflict_target ] conflict_action ]

where conflict_target is pk

    ON CONSTRAINT constraint_name

and conflict_action is one of:

    DO NOTHING
    DO UPDATE SET { <column_name> = { <expression> } |
                    ( <column_name> [, ...] ) = ( { <expression> } [, ...] ) |
                  } [, ...]
              [ WHERE condition ]

How it works

Limits

• In the INSERT ON CONFLICT statement, you must specify all primary key columns.

• When you execute the INSERT ON CONFLICT statement by using the Hologres Query Engine (HQE), data is not written in order. Therefore, you cannot implement the Keep First or Keep Last policy. However, we recommend that you use the Keep Last policy if you want to deduplicate primary key values in the data source. Sample statement:

  -- Retain the last row in duplicate rows.
  set hg_experimental_affect_row_multiple_times_keep_last = on;

Examples

• This section describes how to use the INSERT ON CONFLICT statement.

  1. Create a table and insert data into the table.

     begin ;
     create table test1 (
         a int NOT NULL PRIMARY KEY,
         b int,
         c int
     );
     commit ;
     
     insert into test1 values (1,2,3);
     

  2. Examples in different scenarios:

     Note

     The results in the following scenarios are independent of each other and have no sequential relationship. The table that is created in the preceding step is used in the following scenarios.

     • Scenario 1: Implement the InsertOrIgnore mode. Data is not updated if the inserted primary key value already exists.

       INSERT INTO test1 (a, b, c) VALUES (1, 1, 1)
       ON CONFLICT (a)
       DO NOTHING;
       
       -- After the update, the test1 table contains the following data:
       a	b	c
       1	2	3

     • Scenario 2: Implement the InsertOrUpdate mode to update an entire row of data by using one of the following methods:

       • Method 1: List all columns in the SET..EXCLUDED clause.

         INSERT INTO test1 (a, b, c) VALUES (1, 1, 1)
         ON CONFLICT (a)
         DO UPDATE SET b = EXCLUDED.b, c = EXCLUDED.c;
         
         -- After the update, the test1 table contains the following data:
         a	b	c
         1	1	1

       • Method 2: Use ROW(EXCLUDED.*) to update all columns in a row.

         INSERT INTO test1 (a, b, c)VALUES (1, 1, 1)
         ON CONFLICT (a)
         DO UPDATE SET (a,b,c) = ROW(EXCLUDED.*);
         
         -- After the update, the test1 table contains the following data:
         a	b	c
         1	1	1

     • Scenario 3: Implement the InsertOrUpdate mode to update specified columns. Only data in specified columns is updated.

       -- To update specific columns, specify the columns in the DO UPDATE SET clause.
       INSERT INTO test1 (a, b, c) VALUES (1, 1, 1)
       ON CONFLICT (a)
       DO UPDATE SET b = EXCLUDED.b;
       
       -- Column c in the table is not updated. After the update, the test1 table contains the following data:
       a	b	c
       1	1	3

     • Scenario 4: Implement the InsertOrReplace mode. Insert the null value into columns that are missing in the new data.

       -- To implement the InsertOrReplace mode to insert the null value into the columns that are missing in the new data, manually insert the null value. 
       INSERT INTO test1 (a, b,c) VALUES (1, 1,null)
       ON CONFLICT (a)
       DO UPDATE SET b = EXCLUDED.b,c = EXCLUDED.c;
       
       -- After the update, the test1 table contains the following data:
       a	b	c
       1	1	\N

     • Scenario 5: Insert data from the test2 table into the test1 table.

       -- Create a table named test2 and insert data into the test2 table.
       CREATE TABLE test2 (
           d int NOT NULL PRIMARY KEY,
           e int,
           f int
       );
       INSERT INTO test2 VALUES (1, 5, 6);
       
       
       -- Use the test2 table to overwrite the row in the test1 table that shares the same primary key value with the test2 table.
       INSERT INTO test1 (a, b, c) SELECT d,e,f FROM test2 ON CONFLICT (a) DO UPDATE SET
       (a,b,c) = ROW (excluded.*);
       
       -- After the update, the test1 table contains the following data:
       a	b	c
       1	5	6
       
       -- Use the test2 table to overwrite the row in the test1 table that shares the same primary key value with the test2 table. The column mapping is changed. The data in column e of the test2 table is updated to the column c of the test1 table, and data in column f of the test2 table is updated to the column b of the test1 table.
       INSERT INTO test1 (a, b, c) SELECT d,e,f FROM test2 ON CONFLICT (a) DO UPDATE SET
       (a,c,b) = ROW (excluded.*);
       -- After the update, the test1 table contains the following data:       
       a	b	c
       1	6	5

• This section describes how to optimize the INSERT ON CONFLICT statement for row-oriented tables.

  Hologres optimizes the INSERT ON CONFLICT statement for row-oriented tables. We recommend that you specify columns in the DO UPDATE clause in the same order as those in the INSERT clause and update all columns.

  INSERT INTO test1 (a, b, c) 
  SELECT
      d,e,f
  FROM
      test2
  ON CONFLICT (a)
      DO UPDATE SET
          a = excluded.a,
          b = excluded.b,
          c = excluded.c;
          
  INSERT INTO test1 (a, b, c)
  SELECT d,e,f FROM test2
  ON CONFLICT (a)
  DO UPDATE SET(a,b,c) = ROW (excluded.*)

Errors and troubleshooting

• Problem description

  One of the following errors occurs when you execute the INSERT ON CONFLICT statement to insert data into a table.

  • Error Message 1: duplicate key value violates unique constraint.

  • Error Message 2: Update row with Key (xxx)=(yyy) multiple times.

• Cause 1: The source data contains duplicate rows.

  Hologres is compatible with PostgreSQL and allows you to use the standard PostgreSQL syntax. If you use the standard PostgreSQL syntax to execute the INSERT ON CONFLICT statement to insert data into a table, you must make sure that the source data does not contain duplicate rows. If the source data contains duplicate rows, one of the preceding error messages appears.

  Note

  Duplication in source data indicates that the source data contains duplicate rows. It does not indicate that the destination table contains the same row as the row to be inserted.

  The source data that is specified in the INSERT ON CONFLICT statement contains duplicate rows. Sample statement:

  INSERT INTO test1 VALUES (1, 2, 3), (1, 2, 3)
  ON CONFLICT (a) 
  DO UPDATE SET (a, b, c) = ROW (excluded.*);

  Solution:

  If the source data contains duplicate rows, run the following command to retain the last duplicate row.

  set hg_experimental_affect_row_multiple_times_keep_last = on;

• Cause 2: The source table contains duplicate data because the time to live (TTL) expires.

  A TTL is configured for the source table. Some data whose TTL expires in the source table is not deleted because the TTL is not exact. This may cause duplicate data in the source table. In this case, if data with duplicate primary key values is written into the destination table, one of the preceding error messages appear.

  Solution:

  In Hologres V1.3.23 and later, you can use the following statement to fix this issue. After the statement is executed, the system deletes the duplicate primary key values in the table. The default cleanup policy is Keep Last, which indicates that the last row with the duplicate primary key value is retained, and other rows are deleted.

  Note

  • In principle, a table does not contain duplicate primary key values. Therefore, this statement only clears duplicate primary key values that are caused by TTL expiration.

  • Hologres V1.3.23 and later support this statement. If the version of your Hologres instance is earlier than V1.3.23, upgrade your Hologres instance.

  call hg_remove_duplicated_pk('<schema>.<table_name>');

  Example: The tbl_1 table is the destination table. The tbl_2 table is the source table, and a TTL is configured for this source table. The TTL is set to 300 seconds. Insert an entire row of data in the tbl_2 table into the tbl_1 table. If the TTL expires and the tbl_2 table contains data with duplicate primary key values, data fails to be written and an error occurs.

  
  BEGIN;
  CREATE TABLE tbl_1 (
    a int NOT NULL PRIMARY KEY,
    b int,
    c int
  );
  CREATE TABLE tbl_2 (
    d int NOT NULL PRIMARY KEY,
    e int,
    f int
  );
  CALL set_table_property('tbl_2', 'time_to_live_in_seconds', '300'); 
  COMMIT;
  
  INSERT INTO tbl_1 VALUES (1, 1, 1), (2, 3, 4);
  INSERT INTO tbl_2 VALUES (1, 5, 6);
  -- Insert data into the tbl_2 table after 300 seconds.
  INSERT INTO tbl_2 VALUES (1, 3, 6);
  
  -- Overwrite the row in the tbl_1 table with the row in the tbl_2 table. An error is reported because the tbl_2 table contains data with duplicate primary key values.
  INSERT INTO tbl_1 (a, b, c)
  SELECT
      d,e,f
  FROM
      tbl_2
  ON CONFLICT (a)
      DO UPDATE SET
          (a,b,c) = ROW (excluded.*);
  -- The following error message is returned: ERROR: internal error: Duplicate keys detected when building hash table.
  
  -- Configure the Grand Unified Configuration (GUC) parameter to delete data with duplicate primary key values in the tbl_2 table. The Keep Last policy is used. 
  call hg_remove_duplicated_pk('tbl_2');
  
  -- Insert data into the tbl_1 table again. The operation is successful.