Use INSERT ON CONFLICT to overwrite data - AnalyticDB for PostgreSQL

This topic describes how to use INSERT ON CONFLICT to overwrite data in AnalyticDB for PostgreSQL.

The INSERT ON CONFLICT statement allows you to update an existing row that contains a primary key when you execute the INSERT statement to insert a new row that contains the same primary key. This feature is also known as UPSERT or INSERT OVERWRITE. It is similar to the REPLACE INTO statement of MySQL.

This feature is supported in AnalyticDB for PostgreSQL V6.0 and not supported in AnalyticDB for PostgreSQL V4.3.

Constraints

Only AnalyticDB for PostgreSQL V6.0 supports the overwrite feature. AnalyticDB for PostgreSQL V4.3 does not support this feature.
The table whose data is to be overwritten must be a row store table. The table cannot be a column store table because column store 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 more information about how to upgrade the minor version, see Upgrade the engine version.
Distribution columns and primary key columns cannot be updated in the UPDATE SET clause.
Subqueries cannot be executed in the UPDATE WHERE clause.
The table cannot be an updatable view.
Multiple data records for a primary key cannot be inserted in an INSERT statement. This is a universal limit based on the standard SQL syntax.

Statement

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.

Parameter	Description
conflict_target	If conflict_action is set to Do Update, you must use conflict_target to specify the primary key or unique index column that is used to define a conflict. If conflick_action is set to Do Nothing, you can omit conflict_target.
conflict_action	Specify the action to execute after a conflict. Valid values: DO NOTHING: indicates that the data to insert is discarded if a data conflict occurs in columns specified by conflict_target. DO UPDATE: indicates that the data is overwritten based on the following UPDATE clause if a data conflict occurs in columns specified by conflict_target.

Parameter

Description

conflict_target

If conflict_action is set to Do Update, you must use conflict_target to specify the primary key or unique index column that is used to define a conflict.
If conflick_action is set to Do Nothing, you can omit conflict_target.

conflict_action

Specify the action to execute after a conflict. Valid values:

DO NOTHING: indicates that the data to insert is discarded if a data conflict occurs in columns specified by conflict_target.
DO UPDATE: indicates that the data is overwritten based on the following UPDATE clause if a data conflict occurs in columns specified by conflict_target.

Examples

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

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 the a primary key is 0:

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

Execute the following statement to query the t1 table:

SELECT * FROM t1;

The following result is returned:

 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 the a primary key 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 INSERT ON CONFLICT described in this topic.

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.
The following statement provides an example on this scenario:
```
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.
The following statement provides an example on this scenario:
```
INSERT INTO t1 VALUES (0,2,2,2) ON CONFLICT (a) DO UPDATE SET (b, c, d) = (excluded.b, excluded.c, excluded.d);
```
or
```
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 some columns when a primary key conflict occurs. This is applicable to scenarios where some columns are overwritten based on conflicting data.
For example, execute the following statement to overwrite only data in the c column 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;
```
The following result is returned:
```
 a | b | c | d
---+---+---+---
 0 | 2 | 3 | 2
(1 row)
```
Execute the following INSERT ON CONFLICT statement to update data in some columns. This is applicable to scenarios where some columns are updated based on original data.
For example, execute the following statement to add 1 to data in the d column 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;
```
The following result is returned:
```
 a | b | c | d
---+---+---+---
 0 | 2 | 3 | 3
(1 row)
```
Execute the following INSERT ON CONFLICT statement to set some column values to default values:
For example, execute the following statement to set values in the d column to default values when the primary key conflict occurs. The default value of the d column 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;
```
The following result is returned:
```
 a | b | c | d
---+---+---+---
 0 | 2 | 3 | 0
(1 row)
```
Execute the following statements to insert multiple rows to the t1 table.
- For example, 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;
```
  The following result is returned:
```
 a | b | c | d
---+---+---+---
 0 | 2 | 3 | 0
 1 | 1 | 1 | 1
(2 rows)
```
- For example, execute the following statement to insert two rows of data. For the row which conflicts with the primary key, the data is overwritten. For the row which 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;
```
  The following result is returned:
```
 a | b | c | d
---+---+---+---
 0 | 0 | 0 | 0
 1 | 1 | 1 | 1
 2 | 2 | 2 | 2
(3 rows)
```
Execute the INSERT ON CONFLICT statement to insert data obtained from subqueries to overwrite data when a primary key conflict occurs. This is applicable to merging data from two tables or performing complex INSERT INTO and 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 from 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;
```
The following result is returned:
```
 a | b  | c  | d
---+----+----+----
 0 |  0 |  0 |  0
 1 |  1 |  1 |  1
 2 | 22 | 22 | 22
 3 | 33 | 33 | 33
(4 rows)
```