阿里云首页 云原生数据仓库AnalyticDB PostgreSQL版 相关技术圈

使用COPY ON CONFLICT覆盖导入数据

AnalyticDB PostgreSQL版支持COPY ON CONFLICT覆盖导入数据。目前COPY ON CONFLICT仅支持全表约束检查及全列覆盖写入。

在AnalyticDB PostgreSQL版中,您可以通过COPY快速导入数据,但是在COPY导入数据的过程中,如果数据与表的约束冲突,COPY任务会报错并终止。AnalyticDB PostgreSQL提供了COPY ON CONFLICT功能,支持在约束冲突时进行覆盖写入或忽略写入,避免COPY任务因为约束冲突而失败。

说明

仅内核编译日期为20210528及以后的AnalyticDB PostgreSQL 6.0版实例支持COPY ON CONFLICT功能。为了更好地使用该功能,建议您升级至最新的内核版本,升级内核小版本,请参见版本升级

使用约束

  • 仅支持存储弹性模式实例,暂不支持Serverless版本实例。

  • 目标表需为堆表,不支持AO表(AO表不支持唯一索引,所以不支持AO表)。

  • 仅V6.3.6.1及以上内核版本支持目标表为分区表。如何升级内核版本,请参见版本升级

  • 目标表不支持Updatable View(可更新视图)。

  • COPY ON CONFLICT仅支持COPY FROM,不支持COPY TO。

  • 不支持指定约束索引列,COPY ON CONFLICT默认判断所有约束列。若指定约束索引列,则COPY执行失败,报错信息如下:

    COPY NATION FROM stdin DO ON CONFLICT(n_nationkey) DO UPDATE;
    ERROR:  COPY ON CONFLICT does NOT support CONFLICT index params
  • 不支持指定更新列,COPY ON CONFLICT默认更新所有列。若指定更新列,则COPY执行失败,报错信息如下:

    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

语法

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提供了DO ON CONFLICT DO UPDATE和DO ON CONFLICT DO NOTHING两个子句:

  • DO ON CONFLICT DO UPDATE表示表约束冲突时全列更新。

  • DO ON CONFLICT DO NOTHING表示表约束冲突时忽略输入内容。

示例

  1. 创建一个表NATION,表中包含4列,其中N_NATIONKEY为主键列,具有主键约束,建表语句如下:

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

  2. 通过COPY导入部分数据,COPY语句如下:

    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'
    \.
    说明

    复制以上数据时,请将两列值之间的空格替换为Tab。

  3. 查询NATION表,查看已经导入的数据,查询语句如下:

    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)
  4. 使用COPY语句导入一行主键冲突的数据,COPY语句如下:

    COPY NATION FROM stdin;

    出现>>标志后逐条输入如下内容:

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

    复制以上数据时,请将两列值之间的空格替换为Tab。

    此时执行会产生报错,报错内容如下:

    ERROR:  duplicate key value violates unique constraint "nation_pkey"
    DETAIL:  Key (n_nationkey)=(0) already exists.
    CONTEXT:  COPY nation, line 1
  5. 使用COPY ON CONFLICT语句,在主键冲突的情况下更新数据,COPY ON CONFLICT语句如下:

    COPY NATION FROM stdin DO  ON CONFLICT DO UPDATE;

    出现>>标志后逐条输入如下内容:

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

    复制以上数据时,请将两列值之间的空格替换为Tab。

    此时COPY语句不会产生报错信息,查询NATION表可以看到主键为0的行数据已更新,查询语句如下:

    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)
  6. 使用COPY ON CONFLICT功能,在主键冲突的情况下,忽略输入:

    COPY NATION FROM stdin DO ON CONFLICT DO NOTHING;

    出现>>标志后逐条输入如下内容:

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

    复制以上数据时,请将两列值之间的空格替换为Tab。

    此时COPY语句不会产生报错信息,查询NATION表可以看到主键为1的行数据没有更新,查询语句如下:

    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)