AnalyticDB:Use INSERT ON CONFLICT to overwrite data

Usage notes

• The table whose data is to be overwritten must be a row-oriented table. The table cannot be a column-oriented table because column-oriented tables do not support unique indexes.

• The table can be a partitioned table only when the minor version of the instance is 6.3.6.1 or later. For information about how to update the minor version of an instance, see Update the minor engine version.

• You cannot update distribution key columns or primary key columns in the UPDATE SET clause.

• You cannot execute subqueries in the UPDATE WHERE clause.

• The table cannot be an updatable view.

• You cannot insert multiple data records for a primary key in an INSERT statement. This is a universal limit based on the standard SQL syntax.

SQL syntax

The overwrite syntax is based on the following INSERT statement:

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ ON CONFLICT [ conflict_target ] conflict_action ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
The valid value of conflict_target:
    ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] )
Valid values of conflict_action:
    DO NOTHING
    DO UPDATE SET { column_name = { expression | DEFAULT } |
                    ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] )
                  } [, ...]
              [ WHERE condition ] 

The ON CONFLICT clause can be added to overwrite data. The clause consists of the conflict_target and conflict_action parameters. The following table describes the parameters.

Examples

Execute the following statement to create a table named t1. Configure four columns in the table and specify column a as the primary key column.

CREATE TABLE t1 (a int PRIMARY KEY, b int, c int, d int DEFAULT 0);

Execute the following statement to insert a row of data in which the value of column a is 0:

INSERT INTO t1 VALUES (0,0,0,0);

Execute the following statement to query the t1 table:

SELECT * FROM t1;

Sample result:

 a | b | c | d
---+---+---+---
 0 | 0 | 0 | 0
(1 row)

If the following statement is executed to insert another row of data into the t1 table, and the inserted value of column a is 0, an error is returned:

INSERT INTO t1 VALUES (0,1,1,1);

A similar error message is returned.

ERROR:  duplicate key value violates unique constraint "t1_pkey"
DETAIL:  Key (a)=(0) already exists.

To prevent the preceding error message, you can use the INSERT ON CONFLICT statement.

• Add the following ON CONFLICT DO NOTHING clause to ignore the insertion of the conflicting data. This is applicable to scenarios where conflicting data is discarded.

  Execute the following statement to insert data:

  INSERT INTO t1 VALUES (0,1,1,1) ON CONFLICT DO NOTHING;

  Execute the following statement to query the t1 table:

  SELECT * FROM t1;

  The following result is returned. No operation is performed on the t1 table.

   a | b | c | d
  ---+---+---+---
   0 | 0 | 0 | 0
  (1 row)

• Add the following ON CONFLICT DO UPDATE clause to update non-primary key columns. This is applicable to scenarios where all columns in a table are overwritten.

  Execute one of the following statements to insert data:

  INSERT INTO t1 VALUES (0,2,2,2) ON CONFLICT (a) DO UPDATE SET (b, c, d) = (excluded.b, excluded.c, excluded.d);

  INSERT INTO t1 VALUES (0,2,2,2) ON CONFLICT (a) DO UPDATE SET b = excluded.b, c = excluded.c, d = excluded.d; 

  In the DO UPDATE SET clause, you can use excluded to represent the pseudo-table composed of conflicting data. In the case of a primary key conflict, the column values in the pseudo-table are referenced to overwrite the column values in the t1 table. In the preceding statement, the inserted data (0,2,2,2) constitutes a pseudo-table named excluded that contains a row and four columns. You can use excluded.b, excluded.c, excluded.d to reference the columns in the pseudo-table.

  Execute the following statement to query the t1 table:

  SELECT * FROM t1;

  The following result is returned. The non-primary key columns in the t1 table are updated.

   a | b | c | d
  ---+---+---+---
   0 | 2 | 2 | 2
  (1 row)

In addition to the preceding scenarios, you can use INSERT ON CONFLICT in the following scenarios:

• Use INSERT ON CONFLICT to overwrite data in specific columns when a primary key conflict occurs. This is applicable to scenarios where specific columns are overwritten based on conflicting data.

  Execute the following statement to overwrite data only in column c when a primary key conflict occurs:

  INSERT INTO t1 VALUES (0,0,3,0) ON CONFLICT (a) DO UPDATE SET c = excluded.c;

  Execute the following statement to query the t1 table:

  SELECT * FROM t1;

  Sample result:

   a | b | c | d
  ---+---+---+---
   0 | 2 | 3 | 2
  (1 row)

• Use INSERT ON CONFLICT to update data in specific columns when a primary key conflict occurs. This is applicable to scenarios where specific columns are updated based on original data.

  Execute the following statement to add 1 to the data in column d when a primary key conflict occurs:

  INSERT INTO t1 VALUES (0,0,3,0) ON CONFLICT (a) DO UPDATE SET d = t1.d + 1;

  Execute the following statement to query the t1 table:

  SELECT * FROM t1;

  Sample result:

   a | b | c | d
  ---+---+---+---
   0 | 2 | 3 | 3
  (1 row)

• Use INSERT ON CONFLICT to set specific columns to default values when a primary key conflict occurs.

  Execute the following statement to set values in column d to default values when the primary key conflict occurs. The default value of column d is 0.

  INSERT INTO t1 VALUES (0,0,3,0) ON CONFLICT (a) DO UPDATE SET d = default;

  Execute the following statement to query the t1 table:

  SELECT * FROM t1;

  Sample result:

   a | b | c | d
  ---+---+---+---
   0 | 2 | 3 | 0
  (1 row)

• Use INSERT ON CONFLICT to insert multiple rows of data.

  • Execute the following statement to insert two rows of data. For the row that conflicts with the primary key, no operation is performed. For the row that does not conflict with the primary key, the row is inserted as expected.

    INSERT INTO t1 VALUES (0,0,0,0), (1,1,1,1) ON CONFLICT DO NOTHING;

    Execute the following statement to query the t1 table:

    SELECT * FROM t1;

    Sample result:

     a | b | c | d
    ---+---+---+---
     0 | 2 | 3 | 0
     1 | 1 | 1 | 1
    (2 rows)

  • Execute the following statement to insert two rows of data. For the row that conflicts with the primary key, the data is overwritten. For the row that does not conflict with the primary key, the row is inserted as expected.

    INSERT INTO t1 VALUES (0,0,0,0), (2,2,2,2) ON CONFLICT (a) DO UPDATE SET (b, c, d) = (excluded.b, excluded.c, excluded.d);

    Execute the following statement to query the t1 table:

    SELECT * FROM t1;

    Sample result:

     a | b | c | d
    ---+---+---+---
     0 | 0 | 0 | 0
     1 | 1 | 1 | 1
     2 | 2 | 2 | 2
    (3 rows)

• Use INSERT ON CONFLICT to insert the data that is obtained from subqueries to overwrite original data when a primary key conflict occurs. This is applicable to merging data from two tables or performing complex INSERT INTO SELECT statements.

  Execute the following statement to create a table named t2 that has the same schema as the t1 table:

  CREATE TABLE t2 (like t1);

  Execute the following statement to insert two rows of data to the t2 table:

  INSERT INTO t2 VALUES (2,22,22,22),(3,33,33,33);

  Execute the following statement to insert data of the t2 table into the t1 table. If a primary key conflict occurs, overwrite non-primary key columns.

  INSERT INTO t1 SELECT * FROM t2 ON CONFLICT (a) DO UPDATE SET (b, c, d) = (excluded.b, excluded.c, excluded.d);

  Execute the following statement to query the t1 table:

  SELECT * FROM t1;

  Sample result:

   a | b  | c  | d
  ---+----+----+----
   0 |  0 |  0 |  0
   1 |  1 |  1 |  1
   2 | 22 | 22 | 22
   3 | 33 | 33 | 33
  (4 rows)