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

时间：2024-11-02 18:52:14

云数据库 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 {(SELECT) | (VALUES)}
    TO { 'filename' | STDOUT }
    [ WITHOUT ESCAPING ]
    [ WITH ( option [, ...] ) ]
    | copy_option
    | FIXED FORMATTER ( { column_name( offset, length ) } [, ...] ) [ ( option [, ...] ) | copy_option [  ...] ] ];

COPY TO语法形式约束如下：
(query)与[USING] DELIMITER不兼容，即若COPY TO的数据来自于一个query的查询结果，那么COPY TO语法不能再指定[USING] DELIMITERS语法子句。
对于FIXED FORMATTER语法后面跟随的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 ]
| USEEOF [ 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 
| USEEOF
| FILEHEADER 'header_file_string' 
| FREEZE 
| FORCE_NOT_NULL column_name [, ...]
| FORCE_QUOTE { column_name [, ...] | * }
| BINARY 
|  CS V 
| QUOTE [ AS ] 'quote_character' 
| ESCAPE [ AS ] 'escape_character' 
| EOL 'newline_character'
| ENCODING 'encoding_name' 
| IGNORE_EXTRA_DATA 
| FILL_MISSING_FIELDS [ { 'one' | 'multi' } ]
| COMPATIBLE_ILLEGAL_CHARS 
| DATE_FORMAT 'date_format_string' 
| TIME_FORMAT 'time_format_string' 
| TIMESTAMP_FORMAT 'timestamp_format_string' 
| SMALLDATETIME_FORMAT 'smalldatetime_format_string'
| SKIP int_number
| WHEN { ( start - end ) | column_name } { = | != } 'string'
| SEQUENCE ( { column_name ( integer [, incr] ) [, ...] } )
| FILLER ( { column_name [, ...] } )
| CONSTANT ( { column_name 'constant_string' [, ...] } )

上一篇：云数据库 GAUSSDB-COPY:示例

下一篇：云数据库 GAUSSDB-COPY:示例