All Products
Search
Document Center

Use COPY ON CONFLICT to overwrite data

Last Updated: Aug 12, 2021

COPY ON CONFLICT is a newly supported statement in AnalyticDB for PostgreSQL to overwrite data. When you execute COPY ON CONFLICT statements, you can check constraints only for an entire table and overwrite data only of 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 problem, AnalyticDB for PostgreSQL provides the COPY ON CONFLICT statement.

Note

The COPY ON CONFLICT statement is supported only by AnalyticDB for PostgreSQL V6.0 instances in minor version 20210528 or later. To use this statement, we recommend that you update your instance to the latest minor version. For more information about how to update the minor version, see Upgrade the engine version.

Syntax

COPY ON CONFLICT supports the following COPY FROM 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]

Compared with COPY, COPY ON CONFLICT adds the DO ON CONFLICT DO UPDATE and DO ON CONFLICT DO NOTHING clauses. When data conflicts with table constraints, the DO ON CONFLICT DO UPDATE clause can be used to update an entire column, and the DO ON CONFLICT DO NOTHING clause can be used to ignore insertion.

Constraints

  • 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 cannot be a partition table.

  • The table cannot be an updatable view.

  • COPY ON CONFLICT can include the COPY FROM clause, but not 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. Example:

    COPY NATION FROM stdin DO ON CONFLICT(n_nationkey) DO UPDATE;
    ERROR:  COPY ON CONFLICT does NOT support CONFLICT index params
  • COPY 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

Examples

  1. 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;
    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'
    \.

    Execute the following statement to query the NATION table. The preceding data is imported.

    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'
               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)
  2. Execute the following COPY statement to insert a row of data that conflicts with the primary key:

    COPY NATION FROM stdin;
    0 'GERMANY' 3 'l platelets. regular accounts x-ray: unusual, regular acco'
    \.

    The following error message is returned:

    ERROR:  duplicate key value violates unique constraint "nation_pkey"
    DETAIL:  Key (n_nationkey)=(0) already exists.
    CONTEXT:  COPY nation, line 1
  3. Execute the following COPY ON CONFLICT statement to update the conflicting data:

    COPY NATION FROM stdin DO on conflict DO update;
    0 'GERMANY' 3 'l platelets. regular accounts x-ray: unusual, regular acco'
    \.

    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;
    
     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)
  4. Execute the following COPY ON CONFLICT statement to ignore insertion of the conflicting data:

    COPY NATION FROM stdin DO on conflict DO nothing;
    1 'GERMANY' 3 'l platelets. regular accounts x-ray: unusual, regular acco'
    \.

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