How to use INSERT ON CONFLICT to overwrite data - AnalyticDB

The INSERT ON CONFLICT statement enables an UPSERT operation—"update if exists, insert otherwise"—to prevent write failures from primary key conflicts during data synchronization or bulk imports. This feature is similar to MySQL's REPLACE INTO statement. This topic describes the INSERT ON CONFLICT syntax and provides usage examples.

Precautions

For partitioned tables, the INSERT ON CONFLICT statement is supported only on AnalyticDB for PostgreSQL instances running Minor Kernel Version V6.3.6.1 or later.
Note
You can view the minor version on the Basic Information page of an instance in the AnalyticDB for PostgreSQL console. If your instance does not meet the required versions, update the minor version of the instance.
This feature is supported only for heap tables and Beam tables. It is not supported for columnar tables (AO/AOCS), which do not support unique indexes. To check a table's storage engine, see How can I find out which storage engine a table is using?
A single INSERT statement cannot include multiple rows with the same primary key. This is a constraint of the SQL standard.

SQL syntax

Basic syntax

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT [ conflict_target ] conflict_action

Complete syntax

[ 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 ] [, ...] ]
    
where conflict_target can be one of the following:
    ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] )[ WHERE index_predicate ]
    ON CONSTRAINT constraint_name
    
and conflict_action can be one of the following:
    DO NOTHING
    DO UPDATE SET { column_name = { expression | DEFAULT } |
                    ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] )
                  } [, ...]
              [ WHERE condition ]

The ON CONFLICT clause enables UPSERT operations. This clause consists of conflict_target and conflict_action, which are described as follows:

Parameter

Description

conflict_target

Specifies the conflict target, which determines what constitutes a conflict.

If conflict_action is DO UPDATE, conflict_target must specify the primary key or unique index that cause the conflict.
If conflict_action is DO NOTHING, conflict_target can be omitted.

conflict_action

Specifies the action to perform when a conflict occurs. Valid values:

DO NOTHING: Discards the proposed insertion row when a conflict occurs on the conflict_target.
DO UPDATE: Updates the existing row using the DO UPDATE SET clause when a conflict occurs on the conflict_target. Within this clause, reference the proposed insertion data via the excluded pseudo-table.
Important
- You cannot update the distribution key or primary key in the SET clause of the UPDATE action.
- The WHERE clause of the UPDATE action does not support subqueries.
- The Beam table does not support partial column updates. It supports only full-row updates using DO UPDATE ALL.

Usage examples

Prepare data

Create a table named t1, with column a as the primary key:

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

(Optional) If you use the Beam storage engine and need to perform partial updates on conflict, you must change the table's access method to heap.
```
ALTER TABLE t1 SET ACCESS METHOD heap;
```
Insert a row into table t1 where the primary key a is 0:
```
INSERT INTO t1 VALUES (0,0,0,0);
```

Query the table to view the data:

SELECT * FROM t1;

The following is a sample result:

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

Standard insert

A standard INSERT statement that attempts to insert a row with a duplicate primary key (a = 0) will fail:

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

The operation returns an error:

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

Upsert a single row

To handle primary key conflicts without returning an error, use the ON CONFLICT clause.

Ignore insert on conflict

To ignore an insert operation when a primary key conflict occurs, use the ON CONFLICT DO NOTHING clause.

Insert data:

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

View the table data:

SELECT * FROM t1;

The following is a sample result:

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

Update data on conflict

To overwrite data in case of a primary key conflict, use the ON CONFLICT DO UPDATE clause.

Insert data.
In the DO UPDATE SET clause, the special excluded pseudo-table contains the values from the row proposed for insertion. You can reference these values to update the existing row.
```
INSERT INTO t1 
VALUES (0,1,1,1) 
ON CONFLICT (a) 
DO UPDATE SET 
  (b, c, d) = (excluded.b, excluded.c, excluded.d);
```
Alternatively, you can specify each column individually:
```
INSERT INTO t1 
VALUES (0,1,1,1) 
ON CONFLICT (a) 
DO UPDATE SET 
  b = excluded.b, c = excluded.c, d = excluded.d;
```
View the table data:
```
SELECT * FROM t1;
```
The result shows that the non-primary key columns in table t1 are updated:
```
a | b | c | d
---+---+---+---
 0 | 1 | 1 | 1
(1 row)
```

Update partial columns on conflict

You can also update a subset of columns or update columns based on their existing values. The following examples demonstrate these scenarios.

To verify that the conflict handling logic works as expected, update the test data.
```
UPDATE t1 
SET b = 1, c = 1, d = 1 
WHERE a = 0;
```
Overwrite original data with newly inserted data.
1. Overwrite only the data in the c column. The insertion statement is as follows:
```
INSERT INTO t1 
VALUES (0,3,3,3) 
ON CONFLICT (a) 
DO UPDATE SET 
  c = excluded.c;
```
2. View the table data:
```
SELECT * FROM t1;
```
  The following is a sample result:
```
a | b | c | d
---+---+---+---
 0 | 1 | 3 | 1
(1 row)
```
Update based on the original data.
1. After a primary key conflict, increment the value in the c column by 1. The insertion statement is as follows:
```
INSERT INTO t1 
VALUES (0,0,1,0) 
ON CONFLICT (a) 
DO UPDATE SET 
  c = t1.c + 1;
```
2. View the table data:
```
SELECT * FROM t1;
```
  The following is a sample result:
```
a | b | c | d
---+---+---+---
 0 | 1 | 4 | 1
(1 row)
```

Update to the default value on conflict

In case of a primary key conflict, you can update a column to its default value:

To verify that the conflict handling logic works as expected, update the test data.
```
UPDATE t1 
SET b = 1, c = 1, d = 1 
WHERE a = 0;
```
On a primary key conflict, restore the d column to its default value. The insertion statement is as follows:
```
INSERT INTO t1 
VALUES (0,0,2,2) 
ON CONFLICT (a) 
DO UPDATE SET 
  d = default;
```

View the table data:

SELECT * FROM t1;

The following is a sample result:

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

Upsert multiple rows

Upsert with multiple VALUES clauses

Insert two rows of data. For the row with a primary key conflict, no action is taken. For the row without a primary key conflict, the data is inserted normally.
1. To verify that the conflict handling logic works as expected, update the test data.
```
UPDATE t1 
SET b = 1, c = 1, d = 1 
WHERE a = 0;
```
2. Insert data:
```
INSERT INTO t1 
VALUES (0,2,2,2), (3,3,3,3) 
ON CONFLICT DO NOTHING;
```
3. View the table data:
```
SELECT * FROM t1;
```
  The following is a sample result:
```
a | b | c | d
---+---+---+---
 3 | 3 | 3 | 3
 0 | 1 | 1 | 1
(2 rows)
```
Insert two rows of data. For the row with a primary key conflict, the data is overwritten. For the row without a primary key conflict, the data is inserted normally.
1. Insert data:
```
INSERT INTO t1 
VALUES (0,0,0,0), (4,4,4,4) 
ON CONFLICT (a) 
DO UPDATE SET 
  (b, c, d) = (excluded.b, excluded.c, excluded.d);
```
2. View the table data:
```
SELECT * FROM t1;
```
  The following is a sample result:
```
a | b | c | d
---+---+---+---
 0 | 0 | 0 | 0
 3 | 3 | 3 | 3
 4 | 4 | 4 | 4
(3 rows)
```

Upsert from a subquery

You can also upsert data from a subquery. If a conflict occurs, the existing row is updated. This method is useful for merging tables or for complex INSERT INTO SELECT scenarios. The following is an example.

To verify that the conflict handling logic works as expected, update the test data.
```
DELETE FROM t1 WHERE a != 0; 

UPDATE t1 
SET b = 1, c = 1, d = 1 
WHERE a = 0;
```
Create a table named t2 with the same schema as t1:
```
CREATE TABLE t2 (like t1);
```
Insert two rows of data into table t2. The insertion statement is as follows:
```
INSERT INTO t2 
VALUES (0,11,11,11),(2,22,22,22);
```

Insert data from t2 into t1. On a primary key conflict, the non-primary key columns are overwritten:

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

View the table data:

SELECT * FROM t1;

The following is a sample result:

a | b  | c  | d
---+----+----+----
 2 | 22 | 22 | 22
 0 | 11 | 11 | 11
(2 rows)

Full-row update for Beam tables on conflict

For tables that use the Beam storage engine, you must use INSERT ON CONFLICT DO UPDATE ALL to perform a full-row update when a conflict occurs.

Create a table named beam_test, where column a is the primary key. The statement is as follows:

CREATE TABLE beam_test (
  a int PRIMARY KEY, 
  b int, 
  c int, 
  d int DEFAULT 0
) USING  beam;

Insert data into the beam_test table. The insertion statement is as follows:

INSERT INTO beam_test 
VALUES
  (0, 0, 0, 0),
  (1, 1, 1, 1),
  (2, 2, 2, 2);

View the table data:

SELECT * FROM beam_test;

The following is a sample result:

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

Insert data that conflicts with an existing row:

INSERT INTO beam_test 
VALUES(0, 4, 4, 4) 
ON CONFLICT (a)  
DO UPDATE ALL;

View the table data:

SELECT * FROM beam_test;

The following sample result shows that the conflicting row has been updated:

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

FAQ

How to check a table's storage engine?

You can determine a table's storage engine by querying the pg_class and pg_am system catalogs. Use the following SQL statement, replacing schamename.tablename with your table's fully qualified name:

SELECT
c.oid::regclass AS rel,
coalesce(a.amname, 'heap') AS table_am
FROM pg_class c
LEFT JOIN pg_am a ON a.oid = c.relam
WHERE c.oid = 'schamename.tablename'::regclass
AND c.relkind = 'r';

How to resolve partial-column update errors on Beam tables?

Issue: Performing a partial update via INSERT ON CONFLICT on a Beam table fails with the following error:

ERROR:  INSERT ON CONFLICT DO UPDATE SET is not supported for beam relations
HINT:  Please use INSERT INTO table VALUES(?,?,...) ON CONFLICT DO UPDATE ALL.

This error occurs because the Beam storage engine does not support partial-column updates; it only allows full-row updates.
Solution: To fix this, replace your DO UPDATE SET ... clause with DO UPDATE ALL. This will update the entire conflicting row with the values from your INSERT statement.