更新时间:2026-10-21 GMT+08:00
分享

COPY

功能描述

通过COPY命令实现在表和文件之间复制数据。

COPY FROM从一个文件复制数据到一个表,COPY TO把一个表的数据复制到一个文件。

注意事项

  • 当参数enable_copy_server_files关闭时,只允许初始用户执行COPY FROM FILENAME或COPY TO FILENAME命令,当参数enable_copy_server_files打开时,允许具有SYSADMIN权限的用户或继承了内置角色gs_role_copy_files权限的用户执行,但默认禁止对数据库配置文件、密钥文件、证书文件和审计日志执行COPY FROM FILENAME或COPY TO FILENAME,以防止用户越权查看或修改敏感文件。
  • 数据库管理员可以通过GUC参数safe_data_path设置所有用户可以导入导出路径的白名单,白名单默认生效。COPY使用的文件路径必须为设置路径的子路径。将此GUC参数为空时,不对用户使用的路径进行拦截,即白名单不生效,请谨慎进行设置。该参数会对COPY使用路径中的相对路径进行报错处理。
  • COPY只能用于表,不能用于视图。
  • COPY TO需要读取的表的SELECT权限,COPY FROM需要插入的表的INSERT权限。
  • 如果声明了一个字段列表,COPY将只在文件和表之间复制已声明字段的数据。如果表中有任何不在字段列表里的字段,COPY FROM将为那些字段插入缺省值。
  • 如果声明了数据源文件,服务器必须可以访问该文件;如果指定了STDIN,数据将在客户前端和服务器之间流动,输入时,表的列与列之间使用TAB键分隔,在新的一行中以反斜杠和句点(\.)表示输入结束。
  • 如果数据文件的任意行包含比预期多或者少的字段,COPY FROM将抛出一个错误。
  • 数据的结束可以用一个只包含反斜杠和句点(\.)的行表示。如果从文件中读取数据,数据结束的标记是不必要的;如果在客户端应用之间复制数据,必须要有结束标记。
  • COPY FROM中\N为空字符串,如果要输入实际数据值\N ,使用\\N。
  • COPY FROM不支持在导入过程中对数据做预处理(比如说表达式运算,填充指定默认值等)。如果需要在导入过程中对数据做预处理,用户需先把数据导入到临时表中,然后执行SQL语句通过运算插入到表中,但此方法会导致I/O膨胀,降低导入性能。
  • COPY FROM在遇到数据格式错误时会回滚事务,但没有足够的错误信息,不方便用户从大量的原始数据中定位错误数据。
  • COPY FROM/TO适合低并发,本地小数据量导入导出。
  • COPY命令中,生成列不能出现在指定列的列表中。使用COPY… TO导出数据时,如果没有指定列的列表,则该表的所有列除了生成列都会被导出。COPY… FROM导入数据时,生成列会自动更新,并像普通列一样保存。
  • COPY是服务端命令,执行环境和数据库服务端进程保持一致;\COPY是客户端元命令,执行环境和客户端gsql保持一致。需要注意的是,当在云上环境中使用数据库和gsql时,COPY命令和\COPY命令都使用云上环境的路径;当在云上环境中使用数据库,在非云上环境使用gsql时,COPY命令使用云上环境内的路径,\COPY命令则使用其他环境的路径。
  • 在导出float类型的数据时,推荐将extra_float_digits设为3,避免有效位数丢失造成导出导入前后数据不一致。
  • 不建议在GUC参数lower_case_table_names不同的数据库之间进行COPY FROM或COPY TO操作。
  • 含零字符('\0' ASCII码为0)数据进行COPY FROM或COPY TO操作时存在数据截断。
  • 使用\copy命令时,通过detail_info信息作为查询条件查询审计信息时要忽略大小写,SQL语句会被转换,审计中展示的是COPY关键字。

语法格式

  • 从一个文件复制数据到一个表。
    COPY table_name [ ( column_name [, ...] ) ] [WITH OIDS]
        FROM { 'filename' | STDIN } [LOAD] [ LOAD_DISCARD 'discard_path'] [LOAD_BAD 'bad_path']
        [ LOG ERRORS | LOG ERRORS DATA ]
        [ REJECT LIMIT 'limit' ]
        [ [ WITH ] ( option [, ...] ) | copy_option [, ...] ];

    上述语法中copy_option与option语法不兼容。

  • 把一个表的数据复制到一个文件。
    COPY table_name [ ( column_name [, ...] ) ] [WITH OIDS]
        TO { 'filename' | STDOUT } [LOAD]
      [ [ WITH ] ( option [ ...] ) | copy_option [, ...] ];
    
    COPY query
        TO { 'filename' | STDOUT }
      [ [ WITH ] ( option [ ...] ) | copy_option [, ...] ];

    copy_option是指COPY原生的参数形式,而option是兼容外表导入的参数形式。

    其中可选参数option子句语法为:
    { FORMAT 'format_name'
    | DELIMITER 'delimiter_character'
    | OIDS [ boolean ] 
    | NULL  'null_string'
    | USEEOF [ boolean ]
    | ESCAPE 'escape_character'
    | ENCODING 'encoding_name'
    | FORCE_NOT_NULL ( column_name [, ...] )
    | DATE_FORMAT 'date_format_string'
    | SMALLDATETIME_FORMAT 'smalldatetime_format_string'
    | SKIP int_number
    | BEGIN_LINE_NO begin_line_number
    | LIMIT limit_line_number
    | ROWS commit_row_number
    | SKIP_CONSTRAINT_ERRORS }
    其中可选参数copy_option子句语法为:
    { NULL [ AS ] 'null_string' 
    | FORCE NOT NULL column_name [, ...]
    | BINARY 
    | CSV 
    | ESCAPE [ AS ] 'escape_character' 
    | ENCODING 'encoding_name'
    | DELIMITER [ AS ] 'delimiter_character'
    | ROWS commit_row_number
    | DATE_FORMAT 'date_format_string' 
    | SMALLDATETIME_FORMAT 'smalldatetime_format_string'
    | SKIP int_number
    | STARTS begin_line_number
    | LIMIT limit_line_number
    | SKIP_CONSTRAINT_ERRORS
    | WHEN { ( start - end ) | column_name } { = | != } 'string'
    | SEQUENCE ( { column_name ( integer [, incr] ) [, ...] } ) }

参数说明

  • query

    其结果将被复制。

    取值范围:一个必须用圆括号包围的SELECT或VALUES命令。

  • table_name

    表的名称(可以有模式修饰)。

    取值范围:已存在的表名。

  • column_name

    可选的待复制字段列表。

    取值范围:如果没有声明字段列表,将使用所有字段。

  • STDIN

    声明输入是来自标准输入。输入时,表的列与列之间使用TAB键分隔,在新的一行中以反斜杠和句点(\.)表示输入结束。

    在并行导入模式下,若同时通过标准输入(STDIN)导入数据,客户端会以多线程竞争锁的方式进行数据发送。这可能导致部分线程事务数据提交失败,造成数据仅部分导入,且后续无法对失败数据进行重试导入。因此,不建议在表内有数据的情况下,使用标准输入的方式进行并行导入操作。

  • STDOUT

    声明输出打印到标准输出。

  • LOAD

    内部预留接口,不推荐用户直接使用。

  • LOAD_DISCARD 'discard_path'

    内部预留接口,不推荐用户直接使用。

  • LOAD_BAD 'bad_path'

    内部预留接口,不推荐用户直接使用。

  • LOG ERRORS

    若指定,则开启对于COPY FROM语句中数据类型错误的容错机制。

    此容错选项的使用限制如下:
    • 此容错机制仅捕捉COPY FROM过程中数据库主节点上数据解析过程中相关的数据类型错误(DATA_EXCEPTION)。
    • 此容错选项默认不支持对约束冲突进行容错。如需要对约束冲突进行容错,可额外使用语法SKIP_CONSTRAINT_ERRORS进行导入即可。
      • 支持的约束冲突类型包括:非空约束、条件约束、主键约束、唯一性约束以及唯一性索引。
      • 该功能仅在集中式的M兼容模式下有效。
      • 该功能下数据导入过程会从批量插入变为单行插入,对应的导入性能会有所劣化。
      • 该功能下UB-Tree的索引构建过程会从批量构建变为单行构建,对应的索引构建性能会有所劣化。
    • 从低版本滚动升级到此版本时,所有节点升级完成之前禁止使用COPY容错导入能力。
    • 建议将backtrace_min_messages参数值设置到ERROR以上,否则会对性能有影响。
  • LOG ERRORS DATA
    LOG ERRORS DATA和LOG ERRORS的区别:
    1. LOG ERRORS DATA会填充容错表的rawrecord字段。
    2. 只有sysadmin权限的用户才能使用LOG ERRORS DATA参数选项。
      • 使用LOG ERRORS DATA时,若错误内容过于复杂,则可能存在写入容错表失败的风险,导致任务失败。
      • 对于某种编码无法识别的错误,对应ERRCODE_CHARACTER_NOT_IN_REPERTOIRE和ERRCODE_UNTRANSLATABLE_CHARACTER两种错误码,不记录rawrecord字段。
      • 如果使用BINARY格式进行导入,若存在错误数据,容错表中将不记录对应的rawrecord字段。
      • 从低版本滚动升级到此版本时,所有节点升级完成之前禁止使用COPY容错导入能力。
  • REJECT LIMIT 'limit'

    对COPY FROM的容错机制设置数值上限,当COPY FROM语句中的数据错误数量超过选项指定条数,则会按照原有机制报错。REJECT LIMIT选项需要与LOG ERRORS选项或者LOG ERRORS DATA选项共同使用,才能开启COPY FROM的容错机制。

    取值范围:[1, 2147483647]的正整数或'unlimited'(无最大值限制)。

    • 若未指定LOG ERRORS选项和LOG ERRORS DATA选项,但使用了REJECT LIMIT选项,则会报错。
    • 若指定LOG ERRORS选项或者LOG ERRORS DATA选项,不显式指定REJECT LIMIT选项,则默认不进行容错。
    • REJECT LIMIT按照执行COPY FROM的数据库主节点上遇到的解析错误进行计数,而不是数据库节点的错误数量。
  • OPTION { option_name ' value ' }

    用于指定兼容外表的各类参数。

    • FORMAT

      数据源文件的格式。

      取值范围:CSV、TEXT、BINARY。

      • CSV格式的文件,可以有效处理数据列中的换行符,但对一些特殊字符处理有欠缺。
      • TEXT格式的文件,可以有效处理一些特殊字符,但无法正确处理数据列中的换行符。
      • BINARY形式的选项会使得所有的数据被存储/读作二进制格式而不是文本。 这比TEXT和CSV格式的要快一些,但是一个BINARY格式文件可移植性比较差。

      缺省值:TEXT

    • OIDS [ boolean ]

      对于包含OID的表(通常是系统表),指定是否对OID隐藏列进行导入导出。

      取值范围:true/on,false/off。

      缺省值:false

    • DELIMITER
      指定数据文件行数据的字段分隔符。
      • 分隔符不能是\r和\n。
      • 分隔符不能为空字符''。
      • 分隔符不能和null参数相同,CSV格式数据的分隔符不能包含双引号。
      • TEXT格式数据的分隔符不能包含:小写字母、数字和特殊字符.\。
      • 数据文件中单行数据长度需<1GB,如果分隔符较长且数据列较多的情况下,会影响导出有效数据的长度。
      • 分隔符可以使用多字符和不可见字符。多字符例如'$^&';不可见字符例如0x07,0x08,0x1b等。

      取值范围:支持多字符分隔符,但分隔符不能超过10个字节。

      缺省值:

      • TEXT格式的默认分隔符是水平制表符(tab)。
      • CSV格式的默认分隔符为“,”。
    • NULL

      用来指定数据文件中空值的表示。

      取值范围:

      • null值不能是\r和\n,最大为100个字符。
      • null值不能和分隔符相同,且CSV格式下不能包含双引号。

      缺省值:

      • CSV格式下默认值是一个没有引号的空字符串。
      • 在TEXT格式下默认值是\n。
    • ESCAPE

      CSV格式下,用来指定逃逸字符,逃逸字符只能指定为单字节字符。

      缺省值:''。当CSV格式下设置为双引号时,会被替换为'\0'。

    • ENCODING 'encoding_name'

      指定文件编码格式名称。

      取值范围:有效的编码格式。

      缺省值:当前编码格式。

    • FORCE_NOT_NULL ( column_name [, ...] )

      在CSV COPY FROM模式下,指定的字段输入不能为空。

      取值范围:已存在的字段。

    • DATE_FORMAT

      导入对于DATE类型指定格式。此参数不支持BINARY格式,会报“cannot specify bulkload compatibility options in BINARY mode”错误信息。此参数仅对COPY FROM导入有效。

      取值范围:合法DATE格式。具体请参见日期时间函数。

    • SMALLDATETIME_FORMAT 'smalldatetime_format_string'

      导入时对于SMALLDATETIME类型指定格式。此参数不支持BINARY格式,会报“cannot specify bulkload compatibility options in BINARY mode”错误信息。此参数仅对COPY FROM导入有效。

      取值范围:合法SMALLDATETIME格式。具体请参见日期时间函数。

    • SKIP int_number

      指定数据导入时,跳过数据文件的前int_number行。等价于copy_option中的SKIP int_number参数。

      取值范围:整数[0, 2147483647]。

    • BEGIN_LINE_NO begin_line_number

      指定输入数据对应在错误表中的起始行号。等价于copy_option中的STARTS begin_line_number参数。

    • LIMIT limit_line_number

      指定数据导入时,从输入数据源中最多处理的行数。等价于copy_option中的LIMIT limit_line_number参数。

      取值范围:整数[1, 2147483647]。

    • ROWS commit_row_number

      内部预留接口,不推荐用户直接使用。

    • USEEOF [ boolean ]

      当设置参数或取值为true/on时,不对导入数据中的“\.”进行报错处理。在对单列表进行导入时,在stdin模式下单独输入"\.",会被视作结束符而不是数据本身。

      取值范围:true/on或者false/off。

      缺省值:false。

    • SKIP_CONSTRAINT_ERRORS

      指定导入数据过程中发生约束冲突时,跳过该错误继续导入剩余的数据,跳过的错误信息可通过gs_copy_error_log系统表查看。SKIP_CONSTRAINT_ERRORS参数需要与LOG ERRORS参数或者LOG ERRORS DATA参数共同使用,才能开启COPY FROM的容错机制。等价于copy_option中的SKIP_CONSTRAINT_ERRORS参数。

  • COPY_OPTION { option_name ' value ' }

    用于指定COPY原生的各类参数。

    • NULL null_string
      用来指定数据文件中空值的表示。

      在使用COPY FROM的时候,任何匹配这个字符串的字符串将被存储为NULL值,所以应该确保指定的字符串和COPY TO相同。

      取值范围:

      • null值不能是\r和\n,最大为100个字符。
      • null值不能和分隔符相同,且CSV格式下不能包含双引号。

      缺省值:

      • 在TEXT格式下默认值是\N。
      • CSV格式下默认值是一个没有引号的空字符串。
    • FORCE NOT NULL column_name [, ...]

      在CSV COPY FROM模式下,指定的字段不为空。若输入为空,则将视为长度为0的字符串。

      取值范围:已存在的字段。

    • BINARY
      使用二进制格式存储和读取,而不是以文本的方式。
      • 在二进制模式下,不能声明DELIMITER、NULL、CSV选项。
      • 指定BINARY类型后,不能再通过option或copy_option指定CSV、TEXT等类型。
      • 如果设置了GUC参数support_binary_copy_version='header_encoding',则通过COPY TO导出的二进制文件头中包含数据库的服务端编码信息,COPY FROM导入二进制文件时对编码信息的一致性进行校验,文件中编码信息须与服务端编码保持一致。
      • 如果设置了GUC参数copy_special_character_version='no_error',则会在导入的过程中屏蔽非法字符编码的校验,将非法编码字符按原样导入,查询时以乱码显示。在了解后果的情况下,请谨慎选择开启此参数。可以在COPY语句中使用LOG ERRORS或LOG ERRORS DATA参数,将错误编码数据记录到错误表中,便于记录与查看。
      • 二进制模式下copy_special_character_version='no_error',仅对TEXT、CHAR、VARCHAR、NVARCHAR2类型的字段生效。
    • CSV

      打开逗号分隔变量(CSV)模式。指定CSV类型后,不能再通过option或copy_option指定BINARY、TEXT等类型。

    • ESCAPE [AS] 'escape_character'

      CSV格式下,用来指定逃逸字符,逃逸字符只能指定为单字节字符。

      缺省值:''。当CSV格式下设置为双引号时,会被替换为'\0'。

    • ENCODING 'encoding_name'

      指定文件编码格式名称。

      取值范围:有效的编码格式。

      缺省值:当前编码格式。

    • DATE_FORMAT 'date_format_string'

      导入时对于DATE类型指定格式。此参数不支持BINARY格式,会报“cannot specify bulkload compatibility options in BINARY mode”错误信息。此参数仅对COPY FROM导入有效。

      取值范围:合法DATE格式。具体请参见日期时间函数。

    • SMALLDATETIME_FORMAT 'smalldatetime_format_string'

      导入时对于SMALLDATETIME类型指定格式。此参数不支持BINARY格式,会报“cannot specify bulkload compatibility options in BINARY mode”错误信息。此参数仅对COPY FROM导入有效。

      取值范围:合法SMALLDATETIME格式。具体请参见日期时间函数。

    • SKIP int_number

      指定数据导入时,跳过数据文件的前int_number行。

      取值范围:整数[0, 2147483647]。

    • STARTS begin_line_number

      指定输入数据对应在错误表中的起始行号。当输入数据中第n行为错误数据时,记录到错误表中lineno为n+begin_line_number。如:当错误数据为1、2、5时,如果配置了STARTS 3,记录到错误表中对应lineno分别为4、5、8。

      取值范围:整数[0, 2147483647]。

    • LIMIT limit_line_number

      指定数据导入时,从输入数据源中最多处理的行数。

      取值范围:整数[1, 2147483647]。

    • ROWS commit_row_number

      内部预留接口,不推荐用户直接使用。

    • SKIP_CONSTRAINT_ERRORS

      指定导入数据过程中发生约束冲突时,跳过该错误继续导入剩余的数据,跳过的错误信息可通过gs_copy_error_log系统表查看。默认不配置。

      如果配置,表示copy、\copy均支持跳过约束冲突的错误。其中约束冲突包括非空约束、条件约束、主键约束、唯一性约束以及唯一索引约束。其功能与A兼容下GUC参数a_format_load_with_constraints_violation设置为"s2"时一致。SKIP_CONSTRAINT_ERRORS参数需要与LOG ERRORS参数或者LOG ERRORS DATA参数共同使用,才能开启COPY FROM的容错机制。

    • WHEN { ( start - end ) | column_name } { = | != } 'string'

      数据导入时,检查导入的每一行数据,只有符合WHEN条件的数据行才导入表中。其中,start参数表示数据文件中进行检查的字节起始位置,end参数表示数据文件中进行检查的字节结束位置。例如:WHEN (1-2) = '12'表示的是满足每行字节1到字节2位置数据为'12'的数据行,才能够被导入表中。

    • SEQUENCE ( { column_name ( integer [, incr] ) [, ...] } )

      数据导入时,SEQUENCE修饰的列,不从数据文件读取数据,通过指定的integer,按照incr递增数值;不指定incr则默认从1开始递增。

      不支持在指定SEQUENCE的场景下进行并行导入操作。

COPY FROM能够识别的特殊反斜杠序列如下所示:
  • \b:反斜杠 (ASCII 8)
  • \f:换页(ASCII 12)
  • \n:换行符 (ASCII 10)
  • \r:回车符 (ASCII 13)
  • \t:水平制表符 (ASCII 9)
  • \v:垂直制表符 (ASCII 11)
  • \digits:反斜杠后面跟着一到三个八进制数,表示ASCII值为该数的字符。
  • \xdigits:反斜杠x后面跟着一个或两个十六进制位声明指定数值编码的字符。

权限控制示例

m_db=# copy t1 from '/home/xy/t1.csv';
ERROR:  COPY to or from a file is prohibited for security concerns
HINT:  Anyone can COPY to stdout or from stdin. gsql's \copy command also works for anyone.
m_db=# grant gs_role_copy_files to xxx;

此错误为非初始用户没有使用copy的权限示例,解决方式为打开enable_copy_server_files参数,则管理员可以使用copy功能,普通用户需要在此基础上加入gs_role_copy_files群组。

示例

--创建SCHEMA。
m_db=# CREATE SCHEMA tpcds;

--创建tpcds.ship_mode表。
m_db=# CREATE TABLE tpcds.ship_mode
(
    SM_SHIP_MODE_SK           INTEGER               NOT NULL,
    SM_SHIP_MODE_ID           CHAR(16)              NOT NULL,
    SM_TYPE                   CHAR(30)                      ,
    SM_CODE                   CHAR(10)                      ,
    SM_CARRIER                CHAR(20)                      ,
    SM_CONTRACT               CHAR(20)
)
;

--向tpcds.ship_mode表插入一条数据。
m_db=# INSERT INTO tpcds.ship_mode VALUES (1,'a','b','c','d','e');

--将tpcds.ship_mode中的数据复制到/home/omm/ds_ship_mode.dat文件中。
m_db=# COPY tpcds.ship_mode TO '/home/omm/ds_ship_mode.dat';

--将tpcds.ship_mode 输出到STDOUT。
m_db=# COPY tpcds.ship_mode TO STDOUT;

--将tpcds.ship_mode 的数据输出到STDOUT,使用参数如下:分隔符为','(delimiter ','),编码格式为UTF8(encoding 'utf8')。
m_db=# COPY tpcds.ship_mode TO STDOUT WITH (delimiter ',', encoding 'utf8');

--将tpcds.ship_mode 的数据输出到STDOUT,使用参数如下:导入格式为CSV(format 'CSV')。
m_db=# COPY tpcds.ship_mode TO STDOUT WITH (format 'CSV');

--创建tpcds.ship_mode_t1表。
m_db=# CREATE TABLE tpcds.ship_mode_t1
(
    SM_SHIP_MODE_SK           INTEGER               NOT NULL,
    SM_SHIP_MODE_ID           CHAR(16)              NOT NULL,
    SM_TYPE                   CHAR(30)                      ,
    SM_CODE                   CHAR(10)                      ,
    SM_CARRIER                CHAR(20)                      ,
    SM_CONTRACT               CHAR(20)
)
;

--从STDIN复制数据到表tpcds.ship_mode_t1。
m_db=# COPY tpcds.ship_mode_t1 FROM STDIN;

--从/home/omm/ds_ship_mode.dat文件复制数据到表tpcds.ship_mode_t1。
m_db=# COPY tpcds.ship_mode_t1 FROM '/home/omm/ds_ship_mode.dat';

--从/home/omm/ds_ship_mode.dat文件复制数据到表tpcds.ship_mode_t1,使用参数如下:导入格式为TEXT(format 'text'),分隔符为'\t'(delimiter E'\t')。
m_db=# COPY tpcds.ship_mode_t1 FROM '/home/omm/ds_ship_mode.dat' WITH(format 'text', delimiter E'\t');

--删除表和SCHEMA。
m_db=# DROP TABLE tpcds.ship_mode;
m_db=# DROP TABLE tpcds.ship_mode_t1;
m_db=# DROP SCHEMA tpcds;

相关文档