copy使用
更新时间 2025-02-14 10:23:35
最近更新时间: 2025-02-14 10:23:35
COPY 用于表和标准文件系统文件之间数据互相复制。
COPY 用于 TeleDB表和标准文件系统文件之间数据互相复制。COPY TO 可以把一个表的内容复制到一个文件，COPY FROM 可以从一个文件复制数据到一个表（数据以追加形式入库），COPY TO 也能复制一个SELECT查询的结果到一个文件。如果指定了一个列清单，COPY将只把指定列的数据复制到文件或者从文件复制数据到指定列。如果表中有列不在列清单中，COPY FROM将会为那些列插入默认值。
使用COPY 时，TeleDB服务器直接从本地一个文件读取或者写入到一个文件。该文件必须是TeleDB用户（运行服务器的用户ID）可访问的并且应该以服务器的视角来指定其名称。
实验表结构及数据
teledb=# select * from t;
id | name | birth | city
-----+------+---------------------+------
1 | 张三 | 2000-12-01 00:00:00 | 北京
2 | 李四 | 1997-03-24 00:00:00 | 上海
3 | 王五 | 2004-09-01 00:00:00 | 广州
(3 rows)
teledb=# \d+ t
Table "public.t"
Column | Type | Collation | Nullable | Default | Storage | S
tats target | Description
--------+--------------------------------+-----------+----------+--------------------------------+----------+--
------------+-------------
id | integer | | not null | nextval('t_int_seq'::regclass) | plain |
|
name | character varying | | | | extended |
|
birth | timestamp(0) without time zone | | | | plain |
|
city | character varying | | | | extended |
|
Indexes:
"t_pkey" PRIMARY KEY, btree ("id")
Distribute By: SHARD(id)
Location Nodes: ALL DATANODEScopy to复制数据到文件中
导出所有列
需要带上文件的绝对路径，不能使用相对路径
teledb=# copy t to '~/t.txt'; ERROR: relative path not allowed for COPY to file teledb=# copy t to '/home/teledb/t.txt'; COPY 3 teledb=# \! cat /home/teledb/t.txt 1 张三 2000-12-01 00:00:00 北京 2 李四 1997-03-24 00:00:00 上海 3 王五 2004-09-01 00:00:00 广州默认生成的文件内容为表的所有列，列与列之间使用tab分隔开来。NULL值生成的值为\N。
导出部分列
teledb=# copy t(id, name) to '/home/teledb/t.txt'; COPY 3 teledb=# \! cat /home/teledb/t.txt 1 张三 2 李四 3 王五只导出id和name列。
导出查询结果
teledb=# copy (select name, birth from t order by birth) to '/home/teledb/t.txt'; COPY 3 teledb=# \! cat /home/teledb/t.txt 李四 1997-03-24 00:00:00 张三 2000-12-01 00:00:00 王五 2004-09-01 00:00:00查询可以是任何复杂查询。
指定生成文件格式
生成csv 格式
teledb=# copy t to '/home/teledb/t.txt' with csv; COPY 3 teledb=# \! cat /home/teledb/t.txt 1,张三,2000-12-01 00:00:00,北京 2,李四,1997-03-24 00:00:00,上海 3,王五,2004-09-01 00:00:00,广州生成二进制格式
teledb=# copy t to '/home/teledb/t.txt' with binary; COPY 3 teledb=# \! cat /home/teledb/t.txt PGCOPY ÿ 张.S ֠北京李.ÿÿ°e꿠上海王.广州ÿÿteledb=#默认为TEXT 格式。
使用delimiter指定列与列之间的分隔符
teledb=# copy t to '/home/teledb/t.txt' with delimiter '@'; COPY 3 teledb=# \! cat /home/teledb/t.txt 1@张三@2000-12-01 00:00:00@北京 2@李四@1997-03-24 00:00:00@上海 3@王五@2004-09-01 00:00:00@广州 teledb=# copy t to '/home/teledb/t.txt' with csv delimiter '@'; COPY 3 teledb=# \! cat /home/teledb/t.txt 1@张三@2000-12-01 00:00:00@北京 2@李四@1997-03-24 00:00:00@上海 3@王五@2004-09-01 00:00:00@广州 teledb=# copy t to '/home/teledb/t.txt' with csv delimiter '@@'; ERROR: COPY delimiter must be a single one-byte character teledb=# copy t to '/home/teledb/t.txt' with binary delimiter '@'; ERROR: cannot specify DELIMITER in BINARY mode指定分隔文件各列的字符。文本格式中默认是一个制表符，而CSV格式中默认是一个逗号。分隔符必须是一个单一的单字节字符，即汉字是不支持的。使用binary格式时不允许这个选项。
NULL 值的处理
teledb=# insert into t(name, birth, city) values('赵六','2000-12-01', null); INSERT 0 1 teledb=# copy t to '/home/teledb/t.txt' with csv null 'NULL'; COPY 4 teledb=# \! cat /home/teledb/t.txt 1,张三,2000-12-01 00:00:00,北京 2,李四,1997-03-24 00:00:00,上海 3,王五,2004-09-01 00:00:00,广州 4,赵六,2000-12-01 00:00:00,NULL记录值为NULL时导出为NULL字符。使用binary格式时不允许这个选项。
生成列标题名
teledb=# copy t to '/home/teledb/t.txt' with csv header; COPY 4 teledb=# \! cat /home/teledb/t.txt id,name,birth,city 1,张三,2000-12-01 00:00:00,北京 2,李四,1997-03-24 00:00:00,上海 3,王五,2004-09-01 00:00:00,广州 4,赵六,2000-12-01 00:00:00,只有使用CSV 格式时才允许这个选项。
导出oids系统列
teledb=# create table t_oids (id int primary key, name varchar, birth date, city varchar) with oids; CREATE TABLE teledb=# \d+ t_oids Table "public.t_oids" Column | Type | Collation | Nullable | Default | Storage | Stats target | Descripti on --------+--------------------------------+-----------+----------+---------+----------+--------------+---------- --- id | integer | | not null | | plain | | name | character varying | | | | extended | | birth | timestamp(0) without time zone | | | | plain | | city | character varying | | | | extended | | Indexes: "t_oids_pkey" PRIMARY KEY, btree (id) Has OIDs: yes Distribute By: SHARD(id) Location Nodes: ALL DATANODES teledb=# insert into t_oids select * from t; INSERT 0 4 teledb=# copy t_oids to '/home/teledb/t.txt' with oids csv; COPY 4 teledb=# \! cat /home/teledb/t.txt 33178,1,张三,2000-12-01 00:00:00,北京 33179,2,李四,1997-03-24 00:00:00,上海 33178,3,王五,2004-09-01 00:00:00,广州 33179,4,赵六,2000-12-01 00:00:00,
创建表时使用了with oids才能使用oids选项
使用quote自定义引用字符
teledb=# update t set city = '"杭州"' where id = 3; UPDATE 1 teledb=# copy t to '/home/teledb/t.txt' with csv; COPY 4 teledb=# \! cat /home/teledb/t.txt 1,张三,2000-12-01 00:00:00,北京 2,李四,1997-03-24 00:00:00,上海 4,赵六,2000-12-01 00:00:00, 3,王五,2004-09-01 00:00:00,"""杭州"""默认引用字符为双引号。
teledb=# update t set city = '"杭州%' where id = 3; UPDATE 1 teledb=# copy t to '/home/teledb/t.txt' with quote '%' csv; COPY 4 teledb=# \! cat /home/teledb/t.txt 1,张三,2000-12-01 00:00:00,北京 2,李四,1997-03-24 00:00:00,上海 4,赵六,2000-12-01 00:00:00, 3,王五,2004-09-01 00:00:00,%"杭州%%%上面指定了引用字符为百分号，系统自动把字段值为%的字符替换为双个%。
teledb=# copy t to '/home/teledb/t.txt' with quote '%'; ERROR: COPY quote available only in CSV mode teledb=# copy t to '/home/teledb/t.txt' with quote '%%' csv; ERROR: COPY quote must be a single one-byte character只有使用CSV 格式时才允许这个选项。
引用字符必须是一个单一的单字节字符，即汉字是不支持的。
使用escape 自定义逃逸符
不指定escape逃逸符，默认和QUOTE值一样。
teledb=# update t set city = '%杭州%' where id = 3; UPDATE 1 teledb=# copy t to '/home/teledb/t.txt' with quote '%' csv; COPY 4 teledb=# \! cat /home/teledb/t.txt 1,张三,2000-12-01 00:00:00,北京 2,李四,1997-03-24 00:00:00,上海 4,赵六,2000-12-01 00:00:00, 3,王五,2004-09-01 00:00:00,%%%杭州%%%指定逃逸符为'@'。
teledb=# copy t to '/home/teledb/t.txt' with quote '%' escape '@' csv; COPY 4 teledb=# \! cat /home/teledb/t.txt 1,张三,2000-12-01 00:00:00,北京 2,李四,1997-03-24 00:00:00,上海 4,赵六,2000-12-01 00:00:00, 3,王五,2004-09-01 00:00:00,%@%杭州@%%这必须是一个单一的单字节字符。
teledb=# copy t to '/home/teledb/t.txt' with quote '%' escape '@@' csv; ERROR: COPY escape must be a single one-byte character只有使用CSV 格式时才允许这个选项。
teledb=# copy t to '/home/teledb/t.txt' with quote '%' escape '@@'; ERROR: COPY quote available only in CSV mode强制给某个列添加引用字符
teledb=# copy t to '/home/teledb/t.txt' (format 'csv', force_quote(name)); COPY 4 teledb=# \! cat /home/teledb/t.txt 1,"张三",2000-12-01 00:00:00,北京 2,"李四",1997-03-24 00:00:00,上海 4,"赵六",2000-12-01 00:00:00, 3,"王五",2004-09-01 00:00:00,%杭州%指定name列强制添加引用字符。
teledb=# copy t to '/home/teledb/t.txt' (format 'text', force_quote(name)); ERROR: COPY force quote available only in CSV mode只有使用CSV格式时才允许这个选项。
使用encoding指定导出文件内容编码
teledb=# copy t to '/home/teledb/t.txt' (encoding utf8); COPY 4导出文件编码为UTF8。
teledb=# copy t to '/home/teledb/t.txt' (encoding gbk); COPY 4导出文件编码为gbk。
使用set client_encoding to gbk; 也可以将文件的内容设置为需要的编码，如下所示。
teledb=# set client_encoding to utf8; SET teledb=# copy t to '/home/teledb/t.txt' with csv; COPY 4
copy from复制文件内容到数据表中
导入所有列
teledb=# \! cat /home/teledb/t.txt 1 张三 2000-12-01 00:00:00 北京 2 李四 1997-03-24 00:00:00 上海 3 王五 2004-09-01 00:00:00 广州 4 赵六 2000-12-01 00:00:00 \N teledb=# truncate table t; TRUNCATE TABLE teledb=# copy t from '/home/teledb/t.txt'; COPY 4 teledb=# select * from t; id | name | birth | city ----+------+---------------------+------ 1 | 张三 | 2000-12-01 00:00:00 | 北京 2 | 李四 | 1997-03-24 00:00:00 | 上海 3 | 王五 | 2004-09-01 00:00:00 | 广州 4 | 赵六 | 2000-12-01 00:00:00 | (4 rows)导入部分指定列
teledb=# copy t(id,name) from '/home/teledb/t.txt'; ERROR: extra data after last expected column CONTEXT: COPY t, line 1: "1 张三 2000-12-01 00:00:00 北京", nodetype:1(1:cn,0:dn)数据文件的列表不能多于要导入的列数，否则会出错。
teledb=# copy t(id,name) to '/home/teledb/t.txt'; COPY 4 teledb=# \! cat /home/teledb/t.txt 1 张三 2 李四 3 王五 4 赵六 teledb=# truncate table t; TRUNCATE TABLE teledb=# copy t(id,name) from '/home/teledb/t.txt'; COPY 4 teledb=# select * from t; id | name | birth | city ----+------+-------+------ 1 | 张三 | | 2 | 李四 | | 3 | 王五 | | 4 | 赵六 | |指定导入文件格式
-- text格式 teledb=# \! cat /home/teledb/t.txt 1 张三 2000-12-01 00:00:00 北京 2 李四 1997-03-24 00:00:00 上海 3 王五 2004-09-01 00:00:00 广州 4 赵六 2000-12-01 00:00:00 \N teledb=# truncate table t; TRUNCATE TABLE teledb=# copy t from '/home/teledb/t.txt' (format 'text'); COPY 4 -- csv格式 teledb=# copy t to '/home/teledb/t.txt' with csv; COPY 4 teledb=# \! cat /home/teledb/t.txt 1,张三,2000-12-01 00:00:00,北京 2,李四,1997-03-24 00:00:00,上海 3,王五,2004-09-01 00:00:00,广州 4,赵六,2000-12-01 00:00:00, teledb=# truncate table t; TRUNCATE TABLE teledb=# copy t from '/home/teledb/t.txt' (format 'csv'); COPY 4 -- binary格式 teledb=# copy t to '/home/teledb/t.txt' with binary; COPY 4 teledb=# truncate table t; TRUNCATE TABLE teledb=# \! od -c /home/teledb/t.txt 0000000 P G C O P Y \n 377 \r \n \0 \0 \0 \0 \0 \0 0000020 \0 \0 \0 \0 004 \0 \0 \0 004 \0 \0 \0 001 \0 \0 \0 0000040 006 345 274 240 344 270 211 \0 \0 \0 \b \0 \0 032 S \f 0000060 326 240 \0 \0 \0 \0 006 345 214 227 344 272 254 \0 004 \0 0000100 \0 \0 004 \0 \0 \0 002 \0 \0 \0 006 346 235 216 345 233 0000120 233 \0 \0 \0 \b 377 377 260 e 352 301 \0 \0 \0 \0 0000140 006 344 270 212 346 265 267 \0 004 \0 \0 \0 004 \0 \0 \0 0000160 003 \0 \0 \0 006 347 216 213 344 272 224 \0 \0 \0 \b \0 0000200 \0 205 372 277 n ` \0 \0 \0 \0 006 345 271 277 345 267 0000220 236 \0 004 \0 \0 \0 004 \0 \0 \0 004 \0 \0 \0 006 350 0000240 265 265 345 205 255 \0 \0 \0 \b \0 \0 032 S \f 326 240 0000260 \0 377 377 377 377 377 377 0000267 teledb=# copy t from '/home/teledb/t.txt' (format 'binary'); COPY 4 teledb=# select * from t; id | name | birth | city ----+------+---------------------+------ 1 | 张三 | 2000-12-01 00:00:00 | 北京 2 | 李四 | 1997-03-24 00:00:00 | 上海 3 | 王五 | 2004-09-01 00:00:00 | 广州 4 | 赵六 | 2000-12-01 00:00:00 | (4 rows)使用delimiter指定列与列之间的分隔符
teledb=# \! cat /home/teledb/t.txt 1%张三%2000-12-01 00:00:00%北京 2%李四%1997-03-24 00:00:00%上海 3%王五%2004-09-01 00:00:00%广州 4%赵六%2000-12-01 00:00:00%\N teledb=# truncate table t; TRUNCATE TABLE teledb=# copy t from '/home/teledb/t.txt' (format 'text', delimiter '%'); COPY 4NULL值处理
teledb=# truncate table t; TRUNCATE TABLE teledb=# \! cat /home/teledb/t.txt 1,张三,2000-12-01 00:00:00,北京 2,李四,1997-03-24 00:00:00,上海 3,王五,2004-09-01 00:00:00,广州 4,赵六,2000-12-01 00:00:00,NULL teledb=# copy t from '/home/teledb/t.txt' (format 'csv', null 'NULL'); COPY 4 teledb=# select * from t; id | name | birth | city ----+------+---------------------+------ 1 | 张三 | 2000-12-01 00:00:00 | 北京 2 | 李四 | 1997-03-24 00:00:00 | 上海 3 | 王五 | 2004-09-01 00:00:00 | 广州 4 | 赵六 | 2000-12-01 00:00:00 | (4 rows)将文件中的NULL字符串当成NULL值处理，SQL Server导出来的文件中把NULL值替换成字符串NULL，所以入库时可以这样处理一下，注意字符串是区分大小写
自定义quote 字符
teledb=# \! cat /home/teledb/t.txt 1,张三,2000-12-01 00:00:00,北京 2,李四,1997-03-24 00:00:00,上海 4,赵六,2000-12-01 00:00:00, 3,王五,2004-09-01 00:00:00,%%%杭州",天津%如果不配置quote 字符则无法导入文件。
teledb=# truncate table t; TRUNCATE TABLE teledb=# copy t from '/home/teledb/t.txt' (format 'csv'); ERROR: unterminated CSV quoted field CONTEXT: COPY t, line 5: "3,王五,2004-09-01 00:00:00,%%%杭州",天津% ", nodetype:1(1:cn,0:dn) teledb=# copy t from '/home/teledb/t.txt' (format 'csv', quote '%'); COPY 4 teledb=# copy t from '/home/teledb/t.txt' (format 'text', quote '%'); ERROR: COPY quote available only in CSV mode只有csv 格式导入时才能配置quote字符。
自定义escape字符
teledb=# \! cat /home/teledb/t.txt 1,张三,2000-12-01 00:00:00,北京 2,李四,1997-03-24 00:00:00,上海 4,赵六,2000-12-01 00:00:00, 3,王五,2004-09-01 00:00:00,"%杭州@",天津" teledb=# truncate table t; TRUNCATE TABLE teledb=# copy t from '/home/teledb/t.txt' (format 'csv'); ERROR: unterminated CSV quoted field CONTEXT: COPY t, line 5: "3,王五,2004-09-01 00:00:00,"%杭州@",天津" ", nodetype:1(1:cn,0:dn) teledb=# copy t from '/home/teledb/t.txt' (format 'csv', escape '@'); COPY 4 teledb=# select * from t; id | name | birth | city ----+------+---------------------+------------- 1 | 张三 | 2000-12-01 00:00:00 | 北京 2 | 李四 | 1997-03-24 00:00:00 | 上海 4 | 赵六 | 2000-12-01 00:00:00 | 3 | 王五 | 2004-09-01 00:00:00 | %杭州",天津 (4 rows)csv header忽略首行
teledb=# \! cat /home/teledb/t.txt id,name,birth,city 1,张三,2000-12-01 00:00:00,北京 2,李四,1997-03-24 00:00:00,上海 4,赵六,2000-12-01 00:00:00, 3,王五,2004-09-01 00:00:00,"%杭州"",天津" teledb=# truncate table t; TRUNCATE TABLE teledb=# copy t from '/home/teledb/t.txt' (format 'csv'); ERROR: invalid input syntax for type numeric: "id" CONTEXT: COPY t, line 1, column id: "id", nodetype:1(1:cn,0:dn) teledb=# copy t from '/home/teledb/t.txt' (format 'csv', header true); COPY 4 teledb=# select * from t; id | name | birth | city ----+------+---------------------+------------- 1 | 张三 | 2000-12-01 00:00:00 | 北京 2 | 李四 | 1997-03-24 00:00:00 | 上海 4 | 赵六 | 2000-12-01 00:00:00 | 3 | 王五 | 2004-09-01 00:00:00 | %杭州",天津 (4 rows)如果不忽略首行，则系统会把首行当成数据，造成导入失败。
导入oid列值
teledb=# \! cat /home/teledb/t.txt 33178,1,张三,2000-12-01 00:00:00,北京 33179,2,李四,1997-03-24 00:00:00,上海 33178,3,王五,2004-09-01 00:00:00,广州 33179,4,赵六,2000-12-01 00:00:00, teledb=# truncate table t_oids; TRUNCATE TABLE teledb=# copy t_oids from '/home/teledb/t.txt' (format 'csv', oids true); COPY 4使用FORCE_NOT_NULL把某列中空值变成长度为0的字符串，而不是NULL值
teledb=# alter table t alter column city set not null; ALTER TABLE teledb=# \! cat /home/teledb/t.txt 1,张三,2000-12-01 00:00:00,北京 2,李四,1997-03-24 00:00:00,上海 3,王五,2004-09-01 00:00:00,广州 4,赵六,2000-12-01 00:00:00, teledb=# copy t from '/home/teledb/t.txt' (format 'csv'); ERROR: node:dn02, backend_pid:21393, nodename:dn02,backend_pid:21393,message:null value in column "city" violates not-null constraint DETAIL: Failing row contains (4, 赵六, 2000-12-01 00:00:00, null).不使用FORCE_NOT_NULL处理的话就变成NULL值。
teledb=# copy t from '/home/teledteledb=# create table t_json(id int,f_json json); CREATE TABLE b/t.txt' (format 'csv', force_not_null(city)); COPY 4 teledb=# select * from t; id | name | birth | city ----+------+---------------------+------ 1 | 张三 | 2000-12-01 00:00:00 | 北京 2 | 李四 | 1997-03-24 00:00:00 | 上海 3 | 王五 | 2004-09-01 00:00:00 | 广州 4 | 赵六 | 2000-12-01 00:00:00 | (4 rows)使用FORCE_NOT_NULL处理就变成长度为0的字符串。
encoding 指定导入文件的编码
copy test from '/home/teledb/test.txt';不指定导入文件的编码格式，则无法正确导入中文字符。
teledb=# copy test from '/home/teledb/test.txt'; ERROR: invalid byte sequence for encoding "UTF8": 0xbf CONTEXT: COPY test, line 3, nodetype:1(1:cn,0:dn) teledb=# copy test from '/home/teledb/test.txt' (encoding gbk); COPY 3 teledb=# select * from test; id | name | age | city ----+-------+-----+------ 1 | 123 | 10 | 2 | asdfa | 15 | taga 3 | 开发 | 10 | (3 rows)
