How to overwrite data using INSERT ON CONFLICT - AnalyticDB

To avoid write failures from primary key conflicts or unique constraint violations during data sync or bulk imports, you can use INSERT ON CONFLICT. It performs a standard UPSERT (update or insert) operation, similar to REPLACE INTO in MySQL. This topic describes how to use INSERT ON CONFLICT, details its syntax, and provides use cases.

Before you start

Take note of the following:

Instance version: INSERT ON CONFLICT on partitioned tables is supported only on AnalyticDB for PostgreSQL instances with a minor engine version of V6.3.6.1 or later.
Supported table types: heap (row-oriented) tables and tables that use the Beam storage engine.
Unsupported table types: column-oriented (AO/AOCS) tables (because they do not support unique indexes).
Same-statement conflicts: You cannot insert multiple rows that conflict on the same primary key within a single INSERT statement, for example, trying to insert primary key 1 twice in the same command. This is a standard SQL limitation.

SQL syntax

The optional ON CONFLICT clause specifies an alternative action to raising a unique violation error.

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

ON CONFLICT lets you update the database. It consists of conflict_target and conflict_action, detailed in the table below:

Parameter

Description

conflict_target

Specifies how to identify a conflict.

It tells the database which unique index to use based on index_column_name you provide. An error is raised if no suitable unique index is found.

Required if you set conflict_action to DO UPDATE. You must specify a primary key or unique index columns to define the conflict. For example, a is the primary key:
```
ON CONFLICT (a) 
DO UPDATE
```
Optional it's DO NOTHING.
- If specified, the action only applies to conflicts on that specific index.
- If omitted, the action applies to a conflict with any usable unique constraint.
```
ON CONFLICT DO NOTHING
```

conflict_action

Specifies what to do when a conflict happens, identified by conflict_target:

DO NOTHING: silently avoids inserting the row.
DO UPDATE: updates the existing row that conflicts with the proposed row. The SET and WHERE clauses have access to:
- The existing row's values, using the table's name (for example, t1.column).
- The proposed row's values, using the special excluded pseudo-table (for example, excluded.column).

Important

DO UPDATE limitations:

You cannot update distribution key or primary key columns.
Subqueries are not supported in the WHERE clause.
Partial column updates are not supported for the Beam storage engine. Use DO UPDATE ALL for a full-column update instead.

How to use

1. Prepare data

Create a sample table and insert some initial data. Column a is the primary key.

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

-- Insert an initial row
INSERT INTO t1 VALUES (0, 0, 0, 0);

-- Confirm the data
SELECT * FROM t1;

Result:

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

(Optional) If you use the Beam storage engine and need to update partial columns on conflict, change the table's storage engine to heap.

ALTER TABLE t1 SET ACCESS METHOD heap;

2. Demo a standard insert conflict

Attempting a regular INSERT with a primary key that already exists will cause an error.

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

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

Case 1. Handle conflicts on a single row

To prevent the error above, use ON CONFLICT.

Not update (`DO NOTHING`)

This action simply discards the new row if its key already exists. The original data remains unchanged.

Insert some data.

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

Query the result.

SELECT * FROM t1;

Data is unchanged.

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

Update all (`DO UPDATE`)

This action updates the existing row with new values.

Insert some data.

In the DO UPDATE SET clause, the special excluded pseudo-table contains the values from the proposed insertion row. You can reference its 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, run the following:

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

Query the result.

SELECT * FROM t1;

The non-primary key columns (b, c, and d) are updated.

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

Partial update

You can also update only specific columns or update a column based on its existing value.

Update only column c with the new value from the excluded row.

-- First, ensure the row has known values for this example
UPDATE t1 SET b = 1, c = 1, d = 1 WHERE a = 0;

-- Now, insert with a conflict, updating only column c
INSERT INTO t1 VALUES (0, 3, 3, 3) ON CONFLICT (a) DO UPDATE SET
  c = excluded.c;

Query the result.

SELECT * FROM t1;

Only c is changed to 3.

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

Update a column based on its original value, for exmaple increment c.

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

Query the result.

SELECT * FROM t1;

c is now 4.

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

Update to default

You can update a column to its default value.

Insert a primary key conflict and update d to its default.

-- First, ensure the row has known values for this example
UPDATE t1 SET b = 1, c = 1, d = 1 WHERE a = 0;

-- Now, insert with a conflict, setting column d to its default
INSERT INTO t1 VALUES (0, 0, 2, 2) ON CONFLICT (a) DO UPDATE SET
  d = DEFAULT;

Query the result.
```
SELECT * FROM t1;
```

d is restored to its default value 0.

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

Case 2. Handle conflicts with multiple rows

This example shows how ON CONFLICT behaves when inserting multiple rows at once, where some rows conflict and others do not.

Multi-row insert

Case 1: `DO NOTHING` with multiple rows

The conflicting row is ignored, and the non-conflicting row is inserted.

Update and insert some data.

-- Reset data for this example
UPDATE t1 SET b = 1, c = 1, d = 1 WHERE a = 0;

-- Insert one conflicting row (a=0) and one new row (a=3)
INSERT INTO t1 VALUES (0, 2, 2, 2), (3, 3, 3, 3) ON CONFLICT DO NOTHING;

Query the result.
```
SELECT * FROM t1;
```

Row a=0 is ignored, row a=4 is added.

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

Case 2: `DO UPDATE` with multiple rows

The conflicting row is updated, and the non-conflicting row is inserted.

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

Query the result.
```
SELECT * FROM t1;
```

Row a=0 is updated, and row a=4 is added.

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

Multi-row insert from a subquery

You can also use ON CONFLICT with an INSERT INTO ... SELECT statement. This is a powerful method for merging tables.

Prepare the tables.

-- Reset table t1
DELETE FROM t1;
INSERT INTO t1 VALUES (0, 1, 1, 1);

-- Create and populate table t2
CREATE TABLE t2 (LIKE t1);
INSERT INTO t2 VALUES (0, 11, 11, 11), (2, 22, 22, 22);

Insert from t2 into t1 with conflict handling.

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

Query the result.

SELECT * FROM t1;

Row a=0 is updated, and row a=2 is inserted.

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

Case 3. Full-column update on a Beam table

If your instance uses the Beam storage engine, use INSERT ON CONFLICT DO UPDATE ALL to update all columns on conflict.

Create a beam table.

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

Insert some test data.

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

Query the result.

SELECT * FROM beam_test;

3 rows are inserted.

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

Perform a full-column update.

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

Query the result.

SELECT * FROM beam_test;

All columns are updated.

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

FAQs

How do I query the storage engine of a table?

Run the following SQL, replacing schemaname.tablename with your table's 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 do I resolve errors from partially updating a Beam table?

You receive 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.

Steps to fix:

The Beam storage engine does not support partial-column updates. You have two options:

Perform a full-column update using INSERT ... ON CONFLICT DO UPDATE ALL.
Change the table's storage method to heap via ALTER TABLE your_table_name SET ACCESS METHOD heap;.

Before you start

SQL syntax

Basic syntax

Complete syntax

How to use

1. Prepare data

2. Demo a standard insert conflict

Case 1. Handle conflicts on a single row

Not update (DO NOTHING)

Update all (DO UPDATE)

Partial update

Update to default

Case 2. Handle conflicts with multiple rows

Multi-row insert

Case 1: DO NOTHING with multiple rows

Case 2: DO UPDATE with multiple rows

Multi-row insert from a subquery

Case 3. Full-column update on a Beam table

FAQs

How do I query the storage engine of a table?

How do I resolve errors from partially updating a Beam table?

Not update (`DO NOTHING`)

Update all (`DO UPDATE`)

Case 1: `DO NOTHING` with multiple rows

Case 2: `DO UPDATE` with multiple rows