使用 COPY 时 TDSQL PostgreSQL版 服务器直接从“本地”一个文件读取或者写入到一个文件。该文件必须是 TDSQL PostgreSQL版 用户（运行服务器的用户 ID）可访问的并且应该以服务器的视角来指定其名称。
一、实验表结构及数据
postgres=# \\d+ t Table "public.t" Column | Type | Modifiers | Storage | Stats target | Description ——–+—————————–+—————+———-+————–+————- f1 | integer | not null | plain | | f2 | character varying(32) | not null | extended | | f3 | timestamp without time zone | default now() | plain | | f4 | integer | | plain | | Has OIDs: yesDistribute By SHARD(f1) Location Nodes: ALL DATANODES
数据测试过程中再录入修改。
postgres=# select * from t; f1 | f2 | f3 | f4 —-+——-+—————————-+—- 3 | pgxz | 2017-10-28 18:24:05.645691 | 1 | tdsql_pg | | 7 2 | | 2017-10-28 18:24:05.643102 | 3(3 rows)
三、导出所有列
postgres=# copy public.t to '/data/pgxz/t.txt';COPY 3postgres=# \\! cat /data/pgxz/t.txt1 tdsql_pg \\N 72 2017-10-28 18:24:05.643102 33 pgxz 2017-10-28 18:24:05.645691 \\N
默认生成的文件内容为表的所有列，列与列之间使用 tab 分隔开来。NULL 值生成的值为 \\N。
四、导出部分列
postgres=# copy public.t(f1,f2) to '/data/pgxz/t.txt'; COPY 3postgres=# \\! cat /data/pgxz/t.txt1 tdsql_pg23 pgxzpostgres=#
只导出 f1 和 f2 列。
五、导出查询结果
postgres=# copy (select f2,f3 from public.t order by f3) to '/data/pgxz/t.txt';COPY 3postgres=# \\! cat /data/pgxz/t.txt 2017-10-28 18:24:05.643102pgxz 2017-10-28 18:24:05.645691tdsql_pg \\Npostgres=#
查询可以是任何复杂查询。
六、指定生成文件格式
·生成 csv 格式
postgres=# copy public.t to '/data/pgxz/t.txt' with csv;COPY 3postgres=# \\! cat /data/pgxz/t.txt1,tdsql_pg,,72,pgxc,2017-10-28 18:24:05.643102,33,pgxz,2017-10-28 18:24:05.645691,
·生成二进制格式
postgres=# copy public.t to '/data/pgxz/t.txt' with binary;COPY 3postgres=# \\1postgres=# \\! cat /data/pgxz/t.txtPGCOPY tdsql_pg
默认为 TEXT 格式。
七、使用 delimiter 指定列与列之间的分隔符
postgres=# copy public.t to '/data/pgxz/t.txt' with delimiter '@';COPY 3postgres=# \\! cat /data/pgxz/t.txt 1@tdsql_pg@\\N@72@pgxc@2017-10-28 18:24:05.643102@33@pgxz@2017-10-28 18:24:05.645691@\\Npostgres=# copy public.t to '/data/pgxz/t.txt' with csv delimiter '@';COPY 3postgres=# \\! cat /data/pgxz/t.txt 1@tdsql_pg@@72@pgxc@2017-10-28 18:24:05.643102@33@pgxz@2017-10-28 18:24:05.645691@postgres=# copy public.t to '/data/pgxz/t.txt' with csv delimiter '@@'; ERROR: COPY delimiter must be a single one-byte characterpostgres=# copy public.t to '/data/pgxz/t.txt' with binary delimiter '@'; ERROR: cannot specify DELIMITER in BINARY mode
指定分隔文件各列的字符。文本格式中默认是一个制表符， 而 CSV 格式中默认是一个逗号。分隔符必须是一个单一的单字节字符，即汉字是不支持的。使用 binary 格式时不允许这个选项。
八、NULL 值的处理
postgres=# copy public.t to '/data/pgxz/t.txt' with NULL 'NULL';COPY 3postgres=# \\! cat /data/pgxz/t.txt1 tdsql_pg NULL 72 pgxc 2017-10-28 18:24:05.643102 33 pgxz 2017-10-28 18:24:05.645691 NULLpostgres=# copy public.t to '/data/pgxz/t.txt' with CSV NULL 'NULL'; COPY 3postgres=# \\! cat /data/pgxz/t.txt 1,tdsql_pg,NULL,72,pgxc,2017-10-28 18:24:05.643102,33,pgxz,2017-10-28 18:24:05.645691,NULLpostgres=# copy public.t to '/data/pgxz/t.txt' with binary NULL 'NULL'; ERROR: cannot specify NULL in BINARY modepostgres=#
记录值为 NULL 时导出为 NULL 字符。使用 binary 格式时不允许这个选项。
九、生成列标题名
postgres=# copy public.t to '/data/pgxz/t.txt' with csv HEADER; COPY 3postgres=# \\! cat /data/pgxz/t.txtf1,f2,f3,f41,tdsql_pg,,72,pgxc,2017-10-28 18:24:05.643102,33,pgxz,2017-10-28 18:24:05.645691,postgres=# copy public.t to '/data/pgxz/t.txt' with HEADER; ERROR: COPY HEADER available only in CSV mode
只有使用 CSV 格式时才允许这个选项。
十、导出 oids 系统列
postgres=# drop table t;DROP TABLEpostgres=# CREATE TABLE t (postgres(# f1 integer NOT NULL,postgres(# f2 text NOT NULL,postgres(# f3 timestamp without time zone,postgres(# f4 integerpostgres(# )postgres-# with oids DISTRIBUTE BY SHARD (f1);CREATE TABLEpostgres=# copy t from '/data/pgxz/t.txt' with csv ; COPY 3postgres=# select * from t; f1 | f2 | f3 | f4 —-+—————+—————————-+—- 1 | tdsql_pg | | 7 2 | pg'", xc | 2017-10-28 18:24:05.643102 | 3 3 | pgxz | 2017-10-28 18:24:05.645691 | (3 rows)postgres=# copy t to '/data/pgxz/t.txt' with oids ; COPY 3postgres=# \\! cat /data/pgxz/t.txt35055 1 tdsql_pg \\N 735056 2 pg'", xc 2017-10-28 18:24:05.643102 335177 3 pgxz 2017-10-28 18:24:05.645691 \\N
创建表时使用了 with oids 才能使用 oids 选项。
十一、使用 quote 自定义引用字符
postgres=# copy t to '/data/pgxz/t.txt' with csv; COPY 3postgres=# \\! cat /data/pgxz/t.txt1,tdsql_pg,,72,"pg'"", xc%",2017-10-28 18:24:05.643102,33,pgxz,2017-10-28 18:24:05.645691,
默认引用字符为“双引号”。
postgres=# copy t to '/data/pgxz/t.txt' with quote '%' csv;COPY 3postgres=# \\! cat /data/pgxz/t.txt1,tdsql_pg,,72,%pg'", xc%%%,2017-10-28 18:24:05.643102,33,pgxz,2017-10-28 18:24:05.645691,
上面指定了引用字符为百分号，系统自动把字段值为%的字符替换为双个%。
postgres=# copy t to '/data/pgxz/t.txt' with quote '%';ERROR: COPY quote available only in CSV mode
只有使用 CSV 格式时才允许这个选项。
postgres=# copy t to '/data/pgxz/t.txt' with quote '%%' csv;ERROR: COPY quote must be a single one-byte characterpostgres=#
引用字符必须是一个单一的单字节字符，即汉字是不支持的。
十二、使用 escape 自定义逃逸符
postgres=# copy t to '/data/pgxz/t.txt' with quote '%' csv; COPY 3postgres=# \\! cat /data/pgxz/t.txt1,tdsql_pg,,72,%pg'", xc%%%,2017-10-28 18:24:05.643102,33,pgxz,2017-10-28 18:24:05.645691,
不指定 escape 逃逸符，默认和 QUOTE 值一样。
postgres=# copy t to '/data/pgxz/t.txt' with quote '%' escape '@' csv;COPY 3postgres=# \\! cat /data/pgxz/t.txt 1,tdsql_pg,,72,%pg'", xc@%%,2017-10-28 18:24:05.643102,33,pgxz,2017-10-28 18:24:05.645691,
指定逃逸符为'@'。
postgres=# copy t to '/data/pgxz/t.txt' with quote '%' escape '@@' csv; ERROR: COPY escape must be a single one-byte character
这必须是一个单一的单字节字符。
postgres=# copy t to '/data/pgxz/t.txt' with quote '%' escape '@'; ERROR: COPY quote available only in CSV mode
只有使用 CSV 格式时才允许这个选项。
十三、强制给某个列添加引用字符
postgres=# copy t to '/data/pgxz/t.txt' (format 'csv',force_quote (f1,f2)); COPY 3postgres=# \\! cat /data/pgxz/t.txt"1","tdsql_pg",,7"2","pg'"", xc%",2017-10-28 18:24:05.643102,3"3","pgxz",2017-10-28 18:24:05.645691,
指定2列强制添加引用字符。
postgres=# copy t to '/data/pgxz/t.txt' (format 'csv',force_quote (f1,f4,f3,f2)); COPY 3postgres=# \\! cat /data/pgxz/t.txt"1","tdsql_pg",,"7""2","pg'"", xc%","2017-10-28 18:24:05.643102","3""3","pgxz","2017-10-28 18:24:05.645691",
指定4列强制添加引用字符，字段的顺序可以任意排列。
postgres=# copy t to '/data/pgxz/t.txt' (format 'text',force_quote (f1,f2,f3,f4)); ERROR: COPY force quote available only in CSV modepostgres=#
只有使用 CSV 格式时才允许这个选项。
十四、使用 encoding 指定导出文件内容编码
postgres=# copy t to '/data/pgxz/t.csv' (encoding utf8); COPY 3
导出文件编码为 UTF8。
postgres=# copy t to '/data/pgxz/t.csv' (encoding gbk); COPY 3postgres=#
导出文件编码为 gbk。
使用 set_client_encoding to gbk; 也可以将文件的内容设置为需要的编码，如下所示。
postgres=# set client_encoding to gbk; SETpostgres=# copy t to '/data/pgxz/t.csv' with csv ;COPY 4postgres=#
十六、导入所有列
postgres=# \\! cat /data/pgxz/t.txt 1 tdsql_pg \\N 72 pg'", xc% 2017-10-28 18:24:05.643102 33 pgxz 2017-10-28 18:24:05.645691 \\Npostgres=# truncate table t;TRUNCATE TABLEpostgres=# copy t from '/data/pgxz/t.txt';COPY 3postgres=# select * from t; f1 | f2 | f3 | f4 —-+—————-+—————————-+—- 1 | tdsql_pg | | 7 2 | pg'", xc% | 2017-10-28 18:24:05.643102 | 3 3 | pgxz | 2017-10-28 18:24:05.645691 | (3 rows)
十七、导入部分指定列
postgres=# copy t(f1,f2) to '/data/pgxz/t.txt'; postgres=# \\! cat /data/pgxz/t.txt1 tdsql_pg2 pg'", xc%3 pgxzpostgres=# truncate table t;TRUNCATE TABLEpostgres=# copy t(f1,f2) from '/data/pgxz/t.txt'; COPY 3postgres=# select * from t; f1 | f2 | f3 | f4 —-+—————-+—————————-+—- 1 | tdsql_pg | 2017-10-30 11:54:16.559579 | 2 | pg'", xc% | 2017-10-30 11:54:16.559579 | 3 | pgxz | 2017-10-30 11:54:16.560283 | (3 rows)
有默认值的字段在没有导入时，会自动的将默认值赋上。
postgres=# \\! cat /data/pgxz/t.txt 1 \\N tdsql_pg2 2017-10-28 18:24:05.643102 pg'", xc%3 2017-10-28 18:24:05.645691 pgxzpostgres=# truncate table t;TRUNCATE TABLEpostgres=# copy t(f1,f3,f2) from '/data/pgxz/t.txt'; COPY 3postgres=# select * from t; f1 | f2 | f3 | f4 —-+—————-+—————————-+—- 1 | tdsql_pg | | 2 | pg'", xc% | 2017-10-28 18:24:05.643102 | 3 | pgxz | 2017-10-28 18:24:05.645691 | (3 rows)
字段的顺序可以任意调整，但需要与导放文件的存放顺序一致。
postgres=# \\! cat /data/pgxz/t.txt; 1 tdsql_pg \\N 7 2 pg'", xc% 2017-10-28 18:24:05.643102 3 3 pgxz 2017-10-28 18:24:05.645691 \\N postgres=# copy t (f1,f2) from '/data/pgxz/t.txt'; ERROR: extra data after last expected column CONTEXT: COPY t, line 1: "1 tdsql_pg \\N 7"
数据文件的列表不能多于要导入的列数，否则会出错。
十八、指定导入文件格式
postgres=# \\! cat /data/pgxz/t.txt1 tdsql_pg \\N 72 pg'", xc% 2017-10-28 18:24:05.643102 33 pgxz 2017-10-28 18:24:05.645691 \\Npostgres=# copy t from '/data/pgxz/t.txt' (format 'text');COPY 3TRUNCATE TABLEpostgres=# \\! cat /data/pgxz/t.csv1,tdsql_pg,,72,"pg'"", xc%",2017-10-28 18:24:05.643102,33,pgxz,2017-10-28 18:24:05.645691,postgres=# copy t from '/data/pgxz/t.csv' (format 'csv'); COPY 3postgres=# truncate table t;TRUNCATE TABLEpostgres=# \\! od -c /data/pgxz/t.bin0000000 P G C O P Y \\n 377 \\r \\n \\0 \\0 \\0 \\0 \\0 \\00000020 \\0 \\0 \\0 \\0 004 \\0 \\0 \\0 004 \\0 \\0 \\0 001 \\0 \\0 \\00000040 005 T b a s e 377 377 377 377 \\0 \\0 \\0 004 \\0 \\00000060 \\0 \\a \\0 004 \\0 \\0 \\0 004 \\0 \\0 \\0 002 \\0 \\0 \\0 0160000100 p g ' " , x c % \\0 \\00000120 \\0 \\b \\0 001 377 236 G w 213 ^ \\0 \\0 \\0 004 \\0 \\00000140 \\0 003 \\0 004 \\0 \\0 \\0 004 \\0 \\0 \\0 003 \\0 \\0 \\0 0040000160 p g x z \\0 \\0 \\0 \\b \\0 001 377 236 G w 225 {0000200 377 377 377 377 377 3770000206postgres=# copy t from '/data/pgxz/t.bin' (format 'binary');COPY 3postgres=# select * from t; f1 | f2 | f3 | f4 —-+—————-+—————————-+—- 1 | tdsql_pg | | 7 2 | pg'", xc% | 2017-10-28 18:24:05.643102 | 3 3 | pgxz | 2017-10-28 18:24:05.645691 | (3 rows)
十九、使用 delimiter 指定列与列之间的分隔符
postgres=# \\! cat /data/pgxz/t.txt1%tdsql_pg%\\N%72%pg'", xc\\%%2017-10-28 18:24:05.643102%33%pgxz%2017-10-28 18:24:05.645691%\\Npostgres=# copy t from '/data/pgxz/t.txt' (format 'text',delimiter '%'); COPY 3postgres=# select * from t; f1 | f2 | f3 | f4 —-+—————-+—————————-+—- 1 | tdsql_pg | | 7 2 | pg'", xc% | 2017-10-28 18:24:05.643102 | 3 3 | pgxz | 2017-10-28 18:24:05.645691 | (3 rows)postgres=# \\! cat /data/pgxz/t.csv1%tdsql_pg%%72%"pg'"", xc%"%2017-10-28 18:24:05.643102%33%pgxz%2017-10-28 18:24:05.645691%postgres=# truncate table t;TRUNCATE TABLEpostgres=# copy t from '/data/pgxz/t.csv' (format 'csv',delimiter '%'); COPY 3postgres=# select * from t; f1 | f2 | f3 | f4 —-+—————-+—————————-+—- 1 | tdsql_pg | | 7 2 | pg'", xc% | 2017-10-28 18:24:05.643102 | 3 3 | pgxz | 2017-10-28 18:24:05.645691 | (3 rows)
20、NULL 值处理
postgres=# \\! cat /data/pgxz/t.txt1 tdsql_pg NULL 72 pg'", xc% 2017-10-28 18:24:05.643102 33 pgxz 2017-10-28 18:24:05.645691 NULLpostgres=# copy t from '/data/pgxz/t.txt' (null 'NULL');COPY 3postgres=# select * from t; f1 | f2 | f3 | f4 —-+—————-+—————————-+—- 1 | tdsql_pg | | 7 2 | pg'", xc% | 2017-10-28 18:24:05.643102 | 3 3 | pgxz | 2017-10-28 18:24:05.645691 | (3 rows)
将文件中的 NULL 字符串当成 NULL 值处理，SQL Server 导出来的文件中把 NULL 值替换成字符串 NULL，所以入库时可以这样处理一下，注意字符串是区分大小写，如下面语句导入数据就会出错。
postgres=# copy t from '/data/pgxz/t.txt' (null 'null'); ERROR: invalid input syntax for type timestamp: "NULL" CONTEXT: COPY t, line 1, column f3: "NULL"
21、自定义 quote 字符
postgres=# \\! cat /data/pgxz/t.csv1,tdsql_pg,,72,%pg'", xc%%%,2017-10-28 18:24:05.643102,33,pgxz,2017-10-28 18:24:05.645691,
如果不配置 quote 字符则无法导入文件。
postgres=# copy t from '/data/pgxz/t.csv' (format 'csv');ERROR: unterminated CSV quoted fieldCONTEXT: COPY t, line 4: "2,%pg'", xc%%%,2017-10-28 18:24:05.643102,33,pgxz,2017-10-28 18:24:05.645691,"postgres=# copy t from '/data/pgxz/t.csv' (format 'csv',quote '%');COPY 3postgres=# postgres=# copy t from '/data/pgxz/t.csv' (format 'text',quote '%'); ERROR: COPY quote available only in CSV mode
只有 csv 格式导入时才能配置 quote 字符。
22、自定义 escape 字符
postgres=# \\! cat /data/pgxz/t.csv1,tdsql_pg,,72,"pg'@", xc%",2017-10-28 18:24:05.643102,33,pgxz,2017-10-28 18:24:05.645691,postgres=# copy t from '/data/pgxz/t.csv' (format 'csv'); ERROR: unterminated CSV quoted fieldCONTEXT: COPY t, line 4: "2,"pg'@", xc%",2017-10-28 18:24:05.643102,33,pgxz,2017-10-28 18:24:05.645691,"
不指定 escape 字符时，系统默认就是双写的 quote 字符，如双倍的“双引号”。
postgres=# copy t from '/data/pgxz/t.csv' (format 'csv',escape '@'); COPY 3 postgres=# select * from t; f1 | f2 | f3 | f4 —-+—————-+—————————-+—- 1 | tdsql_pg | | 7 2 | pg'", xc% | 2017-10-28 18:24:05.643102 | 3 3 | pgxz | 2017-10-28 18:24:05.645691 | (3 rows) postgres=#
23、csv header 忽略首行
postgres=# \\! cat /data/pgxz/t.csv;f1,f2,f3,f41,tdsql_pg,,72,"pg'"", xc%",2017-10-28 18:24:05.643102,33,pgxz,2017-10-28 18:24:05.645691,postgres=# copy t from '/data/pgxz/t.csv' (format 'csv');ERROR: invalid input syntax for integer: "f1"CONTEXT: COPY t, line 1, column f1: "f1"postgres=# copy t from '/data/pgxz/t.csv' (format 'csv',header true);COPY 3postgres=# select * from t; f1 | f2 | f3 | f4 —-+—————-+—————————-+—- 1 | tdsql_pg | | 7 2 | pg'", xc% | 2017-10-28 18:24:05.643102 | 3 3 | pgxz | 2017-10-28 18:24:05.645691 | (3 rows)
如果不忽略首行，则系统会把首行当成数据，造成导入失败。
24、导入 oid 列值
postgres=# \\! cat /data/pgxz/t.txt35242 1 tdsql_pg \\N 735243 2 pg'", xc% 2017-10-28 18:24:05.643102 335340 3 pgxz 2017-10-28 18:24:05.645691 \\Npostgres=# truncate table t;TRUNCATE TABLEpostgres=# copy t from '/data/pgxz/t.txt' (oids true);COPY 3postgres=# select oid,* from t; oid | f1 | f2 | f3 | f4 ——-+—-+—————-+—————————-+—- 35242 | 1 | tdsql_pg | | 7 35243 | 2 | pg'", xc% | 2017-10-28 18:24:05.643102 | 3 35340 | 3 | pgxz | 2017-10-28 18:24:05.645691 | (3 rows)
25、使用 FORCE_NOT_NULL 把某列中空值变成长度为0的字符串，而不是 NULL 值
postgres=# truncate table t;TRUNCATE TABLEpostgres=# \\! cat '/data/pgxz/t.csv' ; 1,tdsql_pg,,72,"pg'"", xc%",2017-10-28 18:24:05.643102,33,pgxz,2017-10-28 18:24:05.645691,4,,2017-10-30 16:14:14.954213,4postgres=# copy t from '/data/pgxz/t.csv' (format 'csv');ERROR: node:16386, error null value in column "f2" violates not-null constraintDETAIL: Failing row contains (4, null, 2017-10-30 16:14:14.954213, 4).postgres=# select * from t where f2=''; f1 | f2 | f3 | f4 —-+—-+—-+—-(0 rows)
不使用 FORCE_NOT_NULL 处理的话就变成 NULL 值。
postgres=# truncate table t;TRUNCATE TABLEpostgres=# copy t from '/data/pgxz/t.csv' (format 'csv' ,FORCE_NOT_NULL (f2));COPY 4postgres=# select * from t where f2=''; f1 | f2 | f3 | f4 —-+—-+—————————-+—- 4 | | 2017-10-30 16:14:14.954213 | 4(1 row)
使用 FORCE_NOT_NULL 处理就变成长度为0的字符串。
26、encoding 指定导入文件的编码
postgres=# \\! enca -L zh_CN /data/pgxz/t.txt Simplified Chinese National Standard; GB2312postgres=# copy t from '/data/pgxz/t.txt' ;COPY 4postgres=# select * from t; f1 | f2 | f3 | f4 —-+—————-+—————————-+—- 1 | tdsql_pg | | 7 2 | pg'", xc% | 2017-10-28 18:24:05.643102 | 3 3 | pgxz | 2017-10-28 18:24:05.645691 | 4 | | 2017-10-30 16:41:09.157612 | 4(4 rows)
不指定导入文件的编码格式，则无法正确导入中文字符。
postgres=# truncate table t; TRUNCATE TABLE postgres=# copy t from '/data/pgxz/t.txt' (encoding gbk) ; COPY 4 postgres=# select * from t; f1 | f2 | f3 | f4 —-+—————-+—————————-+—- 1 | tdsql_pg | | 7 2 | pg'", xc% | 2017-10-28 18:24:05.643102 | 3 3 | pgxz | 2017-10-28 18:24:05.645691 | 4 | 腾讯 | 2017-10-30 16:41:09.157612 | 4 (4 rows)
使用 encoding gbk 后便可以正确导入文件的内容，你也可以使用下面的方式转换导入文件的编码后再导入数据。
postgres=# truncate table t;TRUNCATE TABLEpostgres=# \\! enconv -L zh_CN -x UTF-8 /data/pgxz/t.txtpostgres=# copy t from '/data/pgxz/t.txt';COPY 4postgres=# select * from t; f1 | f2 | f3 | f4 —-+—————-+—————————-+—- 1 | tdsql_pg | | 7 2 | pg'", xc% | 2017-10-28 18:24:05.643102 | 3 3 | pgxz | 2017-10-28 18:24:05.645691 | 4 | 腾讯 | 2017-10-30 16:41:09.157612 | 4(4 rows)