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.

  Note

  Hologres V2.1.17 and later support the Serverless Computing feature. The Serverless Computing feature is suitable for scenarios in which you want to import a large amount of data offline, run large-scale extract, transform, and load (ETL) jobs, or query a large amount of data from foreign tables. You can use the Serverless Computing feature to perform the preceding operations based on additional serverless computing resources. This can eliminate the need to reserve additional computing resources for the instances. This improves instance stability and reduces the occurrences of out of memory (OOM) errors. You are charged only for the additional serverless computing resources used by tasks. For more information about the Serverless Computing feature, see Overview of Serverless Computing. For more information about how to use the Serverless Computing feature, see User guide on Serverless Computing.

  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