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 DATANODES

copy 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 4

NULL值处理

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)

应用商城

合作伙伴

开发者

支持与服务

了解天翼云

查看所有产品

分布式融合数据库HTAP

分布式融合数据库HTAP

实验表结构及数据

copy to 复制数据到文件中

导出所有列

导出部分列

导出查询结果

指定生成文件格式

使用delimiter指定列与列之间的分隔符

NULL 值的处理

生成列标题名

导出oids系统列

创建表时使用了with oids才能使用oids选项

copy from复制文件内容到数据表中

活动

应用商城

合作伙伴

开发者

支持与服务

了解天翼云

查看所有产品

分布式融合数据库HTAP

分布式融合数据库HTAP

实验表结构及数据

copy to 复制数据到文件中

导出所有列

导出部分列

导出查询结果

指定生成文件格式

使用delimiter指定列与列之间的分隔符

NULL 值的处理

生成列标题名

导出oids系统列

创建表时使用了with oids才能使用oids选项

copy from复制文件内容到数据表中