Upsert Data with INSERT ON CONFLICT in AnalyticDB PostgreSQL - AnalyticDB for PostgreSQL

INSERT ON CONFLICT performs an upsert—inserting a row when no conflict exists, or updating the existing row when a primary key or unique index conflict is detected. This prevents 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.

Limitations

Partitioned tables: Supported only on instances running minor version V6.3.6.1 or later.
View your instance's minor version on the Basic Information page of the AnalyticDB for PostgreSQL console. To upgrade, see Update the minor version of an instance.
Supported table types: Heap tables and Beam tables only. Columnar tables (AO/AOCS) do not support unique indexes and are not supported.
Duplicate keys in a single statement: A single INSERT statement cannot include multiple rows with the same primary key. This is a SQL standard constraint.

Syntax

Basic syntax

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

Full 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:
    ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
    ON CONSTRAINT constraint_name

and conflict_action can be:
    DO NOTHING
    DO UPDATE SET { column_name = { expression | DEFAULT } |
                    ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] )
                  } [, ...]
              [ WHERE condition ]

Parameters

Parameter	Description
`conflict_target`	Specifies what constitutes a conflict. For `DO NOTHING`: optional—when omitted, all usable constraints and unique indexes are checked. For `DO UPDATE`: required—must identify the primary key or unique index that triggers the conflict.
`conflict_action`	Specifies what to do when a conflict is detected. `DO NOTHING` discards the proposed row. `DO UPDATE` updates the existing row using the `DO UPDATE SET` clause; reference the proposed row's values through the `excluded` pseudo-table. `DO UPDATE ALL` performs a full-row update and is required for Beam tables.

Constraints on `DO UPDATE`:

Cannot update the distribution key or primary key in the SET clause.
The WHERE clause does not support subqueries.
Beam tables do not support partial column updates—use DO UPDATE ALL for full-row updates instead.

Examples

The examples use a table t1 with columns a int PRIMARY KEY, b int, c int, d int DEFAULT 0.

Set up the test table

Create table t1:

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

(Beam tables only) If you use the Beam storage engine and need partial column updates on conflict, change the table's access method to heap:
```
ALTER TABLE t1 SET ACCESS METHOD heap;
```
Insert an initial row:
```
INSERT INTO t1 VALUES (0,0,0,0);
```

Verify the data:

SELECT * FROM t1;

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

The conflict problem

A standard INSERT with a duplicate primary key fails:

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

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

The ON CONFLICT clause handles this by ignoring the conflicting row or updating it.

Ignore the conflicting row

Use DO NOTHING to skip insertion when a conflict occurs. The existing row is unchanged.

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

SELECT * FROM t1;

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

Update all non-primary key columns on conflict

Use DO UPDATE SET to overwrite the existing row. The excluded pseudo-table holds the values from the proposed insert.

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

Alternatively, 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;

SELECT * FROM t1;

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

Update specific columns on conflict

To update only a subset of columns, list only those columns in SET. Columns not listed retain their current values.

Reset the test data first:

UPDATE t1 SET b = 1, c = 1, d = 1 WHERE a = 0;

Overwrite only column `c`:

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

SELECT * FROM t1;

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

Increment a column based on its current value:

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

SELECT * FROM t1;

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

Reset a column to its default value on conflict

Use DEFAULT in SET to restore a column to its defined default.

Reset the test data first:

UPDATE t1 SET b = 1, c = 1, d = 1 WHERE a = 0;

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

SELECT * FROM t1;

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

Column d is reset to 0 (its default), while b and c are unchanged.

Upsert multiple rows

A single INSERT statement can upsert multiple rows. Each row is evaluated independently against the conflict policy.

Reset the test data first:

UPDATE t1 SET b = 1, c = 1, d = 1 WHERE a = 0;

Skip conflicting rows, insert new rows:

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

SELECT * FROM t1;

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

Row (0,2,2,2) is skipped because a = 0 already exists. Row (3,3,3,3) is inserted normally.

Update conflicting rows, insert new rows:

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);

SELECT * FROM t1;

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

Upsert from a subquery

INSERT ON CONFLICT works with INSERT INTO ... SELECT to merge data between tables. This is useful for ETL pipelines and table-merge workflows.

Reset the test data:

DELETE FROM t1 WHERE a != 0;

UPDATE t1 SET b = 1, c = 1, d = 1 WHERE a = 0;

Create a source table and insert data:

CREATE TABLE t2 (LIKE t1);

INSERT INTO t2 VALUES (0,11,11,11), (2,22,22,22);

Merge t2 into t1. On conflict, overwrite the 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);

SELECT * FROM t1;

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

Full-row update for Beam tables

Beam tables do not support partial column updates. Use DO UPDATE ALL to update the entire conflicting row.

Create a Beam table:

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

Insert initial data:

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

Verify the data:

SELECT * FROM beam_test;

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

Upsert a conflicting row using DO UPDATE ALL:

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

Verify the result:

SELECT * FROM beam_test;

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

FAQ

How do I check a table's storage engine?

Query the pg_class and pg_am system catalogs. Replace schemaname.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 = 'schemaname.tablename'::regclass
  AND c.relkind = 'r';

Why does my Beam table fail with "DO UPDATE SET is not supported for beam relations"?

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.

The Beam storage engine supports only full-row updates. Replace DO UPDATE SET ... with DO UPDATE ALL:

-- Before (fails on Beam tables)
INSERT INTO beam_test VALUES (0,4,4,4)
ON CONFLICT (a)
DO UPDATE SET b = excluded.b, c = excluded.c;

-- After (works on Beam tables)
INSERT INTO beam_test VALUES (0,4,4,4)
ON CONFLICT (a)
DO UPDATE ALL;