语法格式

• 从一个文件复制数据到一个表。

  COPY [BINARY] table_name [ ( column_name [, ...] ) ] 
      [ WITH OIDS ]
      FROM { 'filename' | STDIN }
      [ LOAD ]
      [ LOAD_DISCARD 'discard_file_name' ]
      [ LOAD_BAD 'bad_file_name' ]
      [ USEEOF ]
      [ [ USING ] DELIMITERS 'delimiters' ]
      [ WITHOUT ESCAPING ]
      [  LOG  ERRORS | LOG ERRORS DATA ]
      [ REJECT LIMIT 'limit' ]
      [ WITH ]
      [ copy_option [ ...] | ( option [, ...] ) ];
• 把一个表的数据复制到一个文件。

  COPY table_name [ ( column_name [, ...] ) ]
      [ WITH OIDS ]
      TO { 'filename' | STDOUT }
      [ [ USING ] DELIMITERS 'delimiters' ]
      [ WITHOUT ESCAPING ]
      [ WITH ]
      [ copy_option [ ...] | ( option [, ...] ) ];
  
  COPY query {(SELECT) | (VALUES)}
      TO { 'filename' | STDOUT }
      [ WITHOUT ESCAPING ]
      [ WITH ]
      [ copy_option [ ...] | ( option [, ...] ) ];
  

  • COPY TO语法形式约束如下：

    (query)与[USING] DELIMITERS不兼容，即若COPY TO的数据来自于一个query的查询结果，那么COPY TO语法不能再指定[USING] DELIMITERS语法子句。

  • copy_option是指COPY原生的参数形式，而option是兼容外表导入的参数形式。
  • FILLER/SEQUENCE/CONSTANT参数仅在copy_option子句中支持。如果在option子句中传入，会被忽略。
  其中可选参数copy_option子句语法为：

  OIDS
  | DELIMITER [ AS ] 'delimiter_string'
  | NULL [ AS ] 'null_string'
  | HEADER
  | FILEHEADER 'header_file_string'
  | FREEZE 
  | FORCE NOT NULL column_name [, ...]
  | FORCE QUOTE { column_name [, ...] | * }
  | BINARY
  |  CS V
  | FIXED
  | 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'
  | DATEA_FORMAT 'datea_format_string'
  | SMALLDATETIME_FORMAT 'smalldatetime_format_string'
  | SKIP int_number
  | WHEN { { ( start - end ) | column_name } { = | != } 'string' [ AND ...] }
  | SEQUENCE ( { column_name ( integer [, incr] ) [, ...] } )
  | FILLER ( { column_name [, ...] } )
  | CONSTANT ( { column_name 'constant_string' [, ...] } )
  | COPY_CUSTOM_ID 'custom_id_string'
  | TABLE_COMPRESS_CLAUSE( ROW STORE COMPRESS ADVANCED [ MEDIUM | HIGH ] ROW [ON (EXPR)])
  | FORMATTER ( [ column_name( offset, length ) ] [, ...] )
  | TRANSFORM ( [ column_name [ data_type ] [ AS transform_expr ] ] [, ...] )
  | STARTS begin_line_number
  | LIMIT limit_line_number
  | ROWS commit_row_number
  其中可选参数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'
  | DATEA_FORMAT 'datea_format_string'
  | SMALLDATETIME_FORMAT 'smalldatetime_format_string'
  | SKIP int_number
  | COPY_CUSTOM_ID 'custom_id_string'
  | BEGIN_LINE_NO begin_line_number
  | LIMIT limit_line_number
  | ROWS commit_row_number