云数据库 GaussDB-COPY:语法格式

时间：2023-11-01 16:22:51

云数据库 GaussDB

语法格式

从一个文件拷贝数据到一个表。

    
         COPY table_name [ ( column_name [, ...] ) ]     FROM { 'filename' | STDIN }    [ [ USING ] DELIMITERS 'delimiters' ]    [ WITHOUT ESCAPING ]    [ LOG ERRORS ]    [ REJECT LIMIT 'limit' ]    [ [ WITH ] ( option [, ...] ) ]    | copy_option    | TRANSFORM  ( { column_name [ data_type ] [ AS transform_expr ] } [, ...] )    | FIXED FORMATTER ( { column_name( offset, length ) } [, ...] ) [ ( option [, ...] ) | copy_option [  ...] ] ];

语法中的FIXED FORMATTER ( { column_name( offset, length ) } [, ...] )以及 [copy_option [ ...] ] 的无冲突项可以任意排列组合。

把一个表的数据拷贝到一个文件。

    
         COPY table_name [ ( column_name [, ...] ) ]    TO { 'filename' | STDOUT }    [ [ USING ] DELIMITERS 'delimiters' ]    [ WITHOUT ESCAPING ]    [ [ WITH ] ( option [, ...] ) ]    | copy_option    | FIXED FORMATTER ( { column_name( offset, length ) } [, ...] ) [ ( option [, ...] ) | copy_option [  ...] ] ];COPY query    TO { 'filename' | STDOUT }    [ WITHOUT ESCAPING ]    [ [ WITH ] ( option [, ...] ) ]    | copy_option    | FIXED FORMATTER ( { column_name( offset, length ) } [, ...] ) [ ( option [, ...] ) | copy_option [  ...] ] ];

COPY TO语法形式约束如下：
(query)与[USING] DELIMITERS不兼容，即若COPY TO的数据来自于一个query的查询结果，那么COPY TO语法不能再指定[USING] DELIMITERS语法子句。
对于FIXED FORMATTTER语法后面跟随的copy_option是以空格进行分隔的。
copy_option是指COPY原生的参数形式，而option是兼容外表导入的参数形式。
语法中的FIXED FORMATTER ( { column_name( offset, length ) } [, ...] )以及 [ copy_option [ ...] ] 的无冲突项可以任意排列组合。

其中可选参数option子句语法为：

     
          FORMAT 'format_name'| OIDS [ boolean ]| DELIMITER 'delimiter_character'| NULL 'null_string'| HEADER [ boolean ]| FILEHEADER 'header_file_string'| FREEZE [ boolean ]| QUOTE 'quote_character'| ESCAPE 'escape_character'| EOL 'newline_character'| NOESCAPING [ boolean ]| FORCE_QUOTE { ( column_name [, ...] ) | * }| FORCE_NOT_NULL ( column_name [, ...] )| ENCODING 'encoding_name'| IGNORE_EXTRA_DATA [ boolean ]| FILL_MISSING_FIELDS [ boolean ]| COMPATIBLE_ILLEGAL_CHARS [ boolean ]| DATE_FORMAT 'date_format_string'| TIME_FORMAT 'time_format_string'| TIMESTAMP_FORMAT 'timestamp_format_string'| SMALLDATETIME_FORMAT 'smalldatetime_format_string'

其中可选参数copy_option子句语法为：

     
          OIDS | NULL 'null_string' | HEADER | FILEHEADER 'header_file_string' | FREEZE | FORCE_NOT_NULL column_name [, ...]| FORCE_QUOTE { column_name [, ...] | * }| BINARY | CSV | QUOTE [ AS ] 'quote_character' | ESCAPE [ AS ] 'escape_character' | EOL 'newline_character'| ENCODING 'encoding_name' | IGNORE_EXTRA_DATA | FILL_MISSING_FIELDS| COMPATIBLE_ILLEGAL_CHARS | DATE_FORMAT 'date_format_string' | TIME_FORMAT 'time_format_string' | TIMESTAMP_FORMAT 'timestamp_format_string' | SMALLDATETIME_FORMAT 'smalldatetime_format_string'