Overwrite or ignore conflicting data during import using COPY ON CONFLICT - AnalyticDB

When you import data into AnalyticDB for PostgreSQL using COPY, the operation fails if any incoming row conflicts with an existing table constraint. COPY ON CONFLICT extends the standard COPY FROM statement to handle those conflicts without aborting the import — you can overwrite the conflicting row or skip it silently.

COPY ON CONFLICT is supported only on AnalyticDB for PostgreSQL V6.0 instances running minor engine version 20210528 or later. Update your instance to the latest minor engine version before using this feature. For instructions, see Update the minor engine version.

Prerequisites

Before you begin, ensure that you have:

An AnalyticDB for PostgreSQL V6.0 instance running minor engine version 20210528 or later
A heap table with a primary key or unique constraint as the import target

Limitations

The target table must be a heap table. Append-optimized (AO) tables are not supported because they do not have unique indexes.
Partitioned tables are supported only on minor engine version 6.3.6.1 or later. For upgrade instructions, see Update the minor engine version.
The target table cannot be an updatable view.
COPY ON CONFLICT supports only the COPY FROM direction. The COPY TO direction is not supported.
The CONFLICT index parameter is not supported. COPY ON CONFLICT always checks all constraints on the table rather than a specific index. Specifying a conflict index fails with:
```
ERROR:  COPY ON CONFLICT does NOT support CONFLICT index params
```
The UPDATE SET parameter is not supported. COPY ON CONFLICT DO UPDATE always overwrites all columns of the conflicting row rather than a subset. Specifying UPDATE SET fails with:
```
ERROR:  COPY ON CONFLICT does NOT support UPDATE SET targets
```

Syntax

COPY table [(column [, ...])] FROM {'file' | STDIN}
     [ [WITH]
       [BINARY]
       [OIDS]
       [HEADER]
       [DELIMITER [ AS ] 'delimiter']
       [NULL [ AS ] 'null string']
       [ESCAPE [ AS ] 'escape' | 'OFF']
       [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
       [CSV [QUOTE [ AS ] 'quote']
            [FORCE NOT NULL column [, ...]]
       [FILL MISSING FIELDS]
       [[LOG ERRORS]
       SEGMENT REJECT LIMIT count [ROWS | PERCENT] ]
    [DO ON CONFLICT DO UPDATE | NOTHING]

The conflict resolution clause at the end controls what happens when an incoming row matches an existing constraint:

Clause	Behavior
`DO ON CONFLICT DO UPDATE`	Overwrites the existing row with all columns from the incoming data.
`DO ON CONFLICT DO NOTHING`	Skips the incoming row. The existing row is preserved unchanged.

Example

The following example walks through a complete scenario: importing data, triggering a constraint conflict, and resolving it with each clause.

Step 1: Create a table

CREATE TABLE NATION (
    N_NATIONKEY  INTEGER,
    N_NAME       CHAR(25),
    N_REGIONKEY  INTEGER,
    N_COMMENT    VARCHAR(152),
    PRIMARY KEY (N_NATIONKEY)
);

N_NATIONKEY is the primary key and enforces uniqueness across all rows.

Step 2: Import initial data

COPY NATION FROM stdin;

After the >> prompt appears, enter the following rows. Use a tab character between column values.

0	'ALGERIA'	0	'haggle. carefully final deposits detect slyly agai'
1	'ARGENTINA'	1	'al foxes promise slyly according to the regular accounts. bold requests alon'
2	'BRAZIL'	1	'y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly speci'
3	'CANADA'	1	'eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold'
\.

Verify the import:

SELECT * FROM NATION;

Expected output:

 n_nationkey |          n_name           | n_regionkey |                                                 n_comment
-------------+---------------------------+-------------+------------------------------------------------------------------------------------------------------------
           2 | 'BRAZIL'                  |           1 | 'y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly speci'
           3 | 'CANADA'                  |           1 | 'eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold'
           0 | 'ALGERIA'                 |           0 | ' haggle. carefully final deposits detect slyly agai'
           1 | 'ARGENTINA'               |           1 | 'al foxes promise slyly according to the regular accounts. bold requests alon'
(4 rows)

Step 3: Observe a conflict error

Attempting to insert a row with N_NATIONKEY = 0 — which already exists — fails with a standard COPY statement:

COPY NATION FROM stdin;

Enter the following row (tab-separated):

0	'GERMANY'	3	'l platelets. regular accounts x-ray: unusual, regular acco'
\.

The operation fails:

ERROR:  duplicate key value violates unique constraint "nation_pkey"
DETAIL:  Key (n_nationkey)=(0) already exists.
CONTEXT:  COPY nation, line 1

Step 4: Overwrite the conflicting row

Use DO ON CONFLICT DO UPDATE to replace the existing row with the incoming data. When a conflict occurs, COPY ON CONFLICT DO UPDATE overwrites the stored row using all column values from the incoming row.

COPY NATION FROM stdin DO ON CONFLICT DO UPDATE;

Enter the same row (tab-separated):

0	'GERMANY'	3	'l platelets. regular accounts x-ray: unusual, regular acco'
\.

No error is returned. The row with N_NATIONKEY = 0 is updated to reflect the new values:

SELECT * FROM NATION;

 n_nationkey |          n_name           | n_regionkey |                                                 n_comment
-------------+---------------------------+-------------+------------------------------------------------------------------------------------------------------------
           2 | 'BRAZIL'                  |           1 | 'y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly speci'
           3 | 'CANADA'                  |           1 | 'eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold'
           1 | 'ARGENTINA'               |           1 | 'al foxes promise slyly according to the regular accounts. bold requests alon'
           0 | 'GERMANY'                 |           3 | 'l platelets. regular accounts x-ray: unusual, regular acco'
(4 rows)

Step 5: Skip a conflicting row

Use DO ON CONFLICT DO NOTHING to silently ignore the incoming row when a conflict occurs:

COPY NATION FROM stdin DO ON CONFLICT DO NOTHING;

Enter a row with N_NATIONKEY = 1, which conflicts with the existing ARGENTINA row (tab-separated):

1	'GERMANY'	3	'l platelets. regular accounts x-ray: unusual, regular acco'
\.

No error is returned. The row with N_NATIONKEY = 1 is unchanged:

SELECT * FROM NATION;

 n_nationkey |          n_name           | n_regionkey |                                                 n_comment
-------------+---------------------------+-------------+------------------------------------------------------------------------------------------------------------
           2 | 'BRAZIL'                  |           1 | 'y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly speci'
           3 | 'CANADA'                  |           1 | 'eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold'
           1 | 'ARGENTINA'               |           1 | 'al foxes promise slyly according to the regular accounts. bold requests alon'
           0 | 'GERMANY'                 |           3 | 'l platelets. regular accounts x-ray: unusual, regular acco'
(4 rows)