Hologres:INSERT ON CONFLICT (UPSERT)

Scenarios

The INSERT ON CONFLICT command is suitable for scenarios where you import data using SQL statements.

Command introduction

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

• The condition in the INSERT ON CONFLICT statement must include all primary keys.

• When Hologres High-QPS Engine (HQE) executes an INSERT ON CONFLICT statement, it does not guarantee the order of operations. Therefore, you cannot achieve keep-first or keep-last semantics. The behavior is keep-any. In practice, if the data source contains duplicate primary keys that need to be removed, you can use keep-last semantics by running the following command:

  -- Keep the last of the duplicate rows.
  set hg_experimental_affect_row_multiple_times_keep_last = on;

Examples

• Example of how to use the INSERT ON CONFLICT statement:

  Note

  Hologres V2.1.17 and later support Serverless Computing. For scenarios such as large-scale offline data import, large extract, transform, and load (ETL) jobs, and large-volume queries on foreign tables, you can use Serverless Computing to execute these tasks. This feature uses additional serverless resources instead of your instance's own resources. You do not need to reserve extra compute resources for your instance. This significantly improves instance stability, reduces the probability of out-of-memory (OOM) errors, and you are charged only for the individual tasks. For more information about Serverless Computing, see Serverless Computing. For information about how to use Serverless Computing, see Guide to using Serverless Computing.

  1. Prepare a table and data:

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

  2. View examples for different scenarios:

     Note

     The following scenario examples are independent of each other. They are not sequential and are all based on the table and data created in the preceding step.

     • Scenario 1: Implement an InsertOrIgnore operation. If a duplicate primary key exists, the row is not updated.

       INSERT INTO test1 (a, b, c) VALUES (1, 1, 1)
       ON CONFLICT (a)
       DO NOTHING;
       
       -- The data in the test1 table after the update is:
       a	b	c
       1	2	3

     • Scenario 2: Implement an InsertOrUpdate operation for a full-row update. You can use 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;
         
         -- The data in the test1 table after the update is:
         a	b	c
         1	1	1

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

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

     • Scenario 3: Implement an InsertOrUpdate operation for a partial-column update. Only specified columns are updated, and missing columns are not updated.

       -- To perform a partial-column update, list the columns that you want to update after SET.
       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. The data in the test1 table after the update is:
       a	b	c
       1	1	3

     • Scenario 4: Implement an InsertOrReplace operation. This overwrites the entire row. If columns are missing, they are filled with null values.

       -- To implement an InsertOrReplace operation where missing columns are filled with null, you must manually provide null in the insert values.
       INSERT INTO test1 (a, b,c) VALUES (1, 1,null)
       ON CONFLICT (a)
       DO UPDATE SET b = EXCLUDED.b,c = EXCLUDED.c;
       
       -- The data in the test1 table after the update is:
       a	b	c
       1	1	\N

     • Scenario 5: Update data in the test1 table from another table named test2.

       -- Prepare the test2 table and data.
       CREATE TABLE test2 (
           d int NOT NULL PRIMARY KEY,
           e int,
           f int
       );
       INSERT INTO test2 VALUES (1, 5, 6);
       
       
       -- Replace rows in the test1 table with rows from the test2 table that have the same primary key.
       INSERT INTO test1 (a, b, c) SELECT d,e,f FROM test2 ON CONFLICT (a) DO UPDATE SET
       (a,b,c) = ROW (excluded.*);
       
       -- The data in the test1 table after the update is:
       a	b	c
       1	5	6
       
       -- Replace rows in the test1 table with rows from the test2 table that have the same primary key, but adjust the update mapping. Column e of test2 updates column c, and column f of test2 updates column b.
       INSERT INTO test1 (a, b, c) SELECT d,e,f FROM test2 ON CONFLICT (a) DO UPDATE SET
       (a,c,b) = ROW (excluded.*);
       -- The data in the test1 table after the update is:       
       a	b	c
       1	6	5

• Optimization for the INSERT ON CONFLICT statement on row-oriented tables:

  Hologres optimizes updates for row-oriented tables. For the best performance, keep the order of columns in the UPDATE clause consistent with the order in the INSERT clause and perform a full-row update.

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

Common errors