All Products
Search
Document Center

Use COPY ON CONFLICT to overwrite data

Last Updated: Jan 14, 2022

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 to overwrite data or ignore the write operation.

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.

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 can be a partitioned table only when the minor version of the instance is 6.3.6.1 or later. For more information about how to update the minor version, see Upgrade the 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 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

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

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

  2. 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'
    \.
    Note

    When you copy the preceding data, replace the space between the two column values with Tab.

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

    SELECT * from NATION;

    The following result is returned:

     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)
  4. 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'
    \.
    Note

    When 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 1
  5. Execute 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'
    \.
    Note

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

    The following result is returned:

     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)
  6. 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'
    \.
    Note

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

    The following result is returned:

     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)