AnalyticDB for PostgreSQL allows you to overwrite data by using the COPY ON CONFLICT statement. When you execute the COPY ON CONFLICT statement, you can check constraints only for an entire table and overwrite data of only an entire column.
AnalyticDB for PostgreSQL allows you to import data by executing a COPY statement. However, if the data that you want to import conflicts with table constraints, the COPY statement may fail with an error message. To resolve this issue, AnalyticDB for PostgreSQL provides the COPY ON CONFLICT statement to overwrite data or ignore the write operation.
The COPY ON CONFLICT statement is supported only for AnalyticDB for PostgreSQL V6.0 instances of minor version 20210528 or later. To use this statement, we recommend that you update your instance to the latest minor version. For more information, see Update the minor engine version.
Limits
The table to which you want to import data must be a heap table, but not an append-optimized (AO) table. This is because AO 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 information about how to update the minor version of an instance, see Update the minor engine version.
The table cannot be an updatable view.
COPY ON CONFLICT can include only the COPY FROM clause and cannot include the COPY TO clause.
COPY ON CONFLICT does not allow you to specify the CONFLICT index parameter. By default, COPY ON CONFLICT determines all the constrained columns. If the CONFLICT index parameter is specified, the COPY statement fails with an error message.
COPY NATION FROM stdin DO ON CONFLICT(n_nationkey) DO UPDATE; ERROR: COPY ON CONFLICT does NOT support CONFLICT index paramsCOPY ON CONFLICT does not allow you to specify the UPDATE SET parameter. By default, COPY ON CONFLICT updates all columns. If the UPDATE SET parameter is specified, the COPY statement fails with an error message. Example:
COPY NATION FROM stdin DO ON CONFLICT DO UPDATE SET n_nationkey = excluded.n_nationkey; 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]COPY ON CONFLICT supports the DO ON CONFLICT DO UPDATE and DO ON CONFLICT DO NOTHING clauses.
The DO ON CONFLICT DO UPDATE clause is used to update an entire column when data conflicts with table constraints.
The DO ON CONFLICT DO NOTHING clause is used to ignore insertion when data conflicts with table constraints.
Examples
Create a table named NATION. Set four columns in the table and specify N_NATIONKEY as the primary key that enforces constraints to the table.
CREATE TABLE NATION ( N_NATIONKEY INTEGER, N_NAME CHAR(25), N_REGIONKEY INTEGER, N_COMMENT VARCHAR(152), PRIMARY KEY (N_NATIONKEY) );Execute the following COPY statement to import data:
COPY NATION FROM stdin;After the >> flag appears, enter the following rows of data in sequence:
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' \.NoteWhen you copy the preceding data, replace the space between the two column values with Tab.
Execute the following statement to query the NATION table. The preceding data is imported.
SELECT * from NATION;Sample result:
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)Execute the following COPY statement to insert a row of data that conflicts with the primary key:
COPY NATION FROM stdin;After the >> flag appears, enter the following rows of data in sequence:
0 'GERMANY' 3 'l platelets. regular accounts x-ray: unusual, regular acco' \.NoteWhen you copy the preceding data, replace the space between the two column values with Tab.
A similar error message is returned:
ERROR: duplicate key value violates unique constraint "nation_pkey" DETAIL: Key (n_nationkey)=(0) already exists. CONTEXT: COPY nation, line 1Execute the following COPY ON CONFLICT statement to update the conflicting data:
COPY NATION FROM stdin DO ON CONFLICT DO UPDATE;After the >> flag appears, enter the following rows of data in sequence:
0 'GERMANY' 3 'l platelets. regular accounts x-ray: unusual, regular acco' \.NoteWhen you copy the preceding data, replace the space between the two column values with Tab.
No error message is returned. Execute the following statement to query the NATION table. A row of data with the primary key value of 0 is updated in the table.
SELECT * FROM NATION;Sample result:
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)Execute the following COPY ON CONFLICT statement to ignore insertion of the conflicting data:
COPY NATION FROM stdin DO ON CONFLICT DO NOTHING;After the >> flag appears, enter the following rows of data in sequence:
1 'GERMANY' 3 'l platelets. regular accounts x-ray: unusual, regular acco' \.NoteWhen you copy the preceding data, replace the space between the two column values with Tab.
No error message is returned. Execute the following statement to query the NATION table. The row of data with the primary key value of 1 is not updated in the table.
SELECT * FROM NATION;Sample result:
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)