腾讯云数据仓库 TCHouse-P copy 的使用-开发指南-文档中心-腾讯云

COPY 用于 TBase 表和标准文件系统文件之间数据互相复制。COPY TO 可以把一个表的内容复制到一个文件，COPY FROM 可以从一个文件复制数据到一个表（数据以追加形式入库），COPY TO 也能复制一个 SELECT 查询的结果到一个文件。如果指定了一个列表，COPY 将只把指定列的数据复制到文件或者从文件复制数据到指定列。如果表中有列不在列表中，COPY FROM 将会为那些列插入默认值。 使用 COPY 时 TBase 服务器直接从“本地”一个文件读取或者写入到一个文件。该文件必须是 TBase 用户（运行服务器的用户 ID）可访问的并且应该以服务器的视角来指定其名称。
实验表结构及数据
```sql
\\d+ t
Column
Type
Modifiers
Storage
Stats target
Description
f1
integer
not null
plain
﻿
﻿
f2
character
not null
extended
﻿
﻿
f3
timestamp
default now()
plain
﻿
﻿
f4
integer
﻿
plain
﻿
﻿
数据测试过程可以行再录入修改
select * from t;
f1
f2
f3
f4
3
pgxz
2017-10-28 18:24:05.645691
﻿
1
Tbase
﻿
7
2
﻿
2017-10-28 18:24:05.643102
3
COPY TO 用法详解--复制数据到文件中
导出所有列
copy public.t to '/data/pgxz/t.txt';
cat /data/pgxz/t.txt
默认生成的文件内容为表的所有列，列与列之间使用 tab 分隔开来。NULL 值生成的值为\\N。
导出部分列
copy public.t(f1,f2) to '/data/pgxz/t.txt';
cat /data/pgxz/t.txt
只导出 f1 和 f2 列
导出查询结果
copy (select f2,f3 from public.t order by f3) to '/data/pgxz/t.txt';
cat /data/pgxz/t.txt
查询可以是任何复杂查询
指定生成文件格式
生成 csv 格式
```sql
copy public.t to '/data/pgxz/t.txt' with csv;
cat /data/pgxz/t.txt
生成二进制格式
copy public.t to '/data/pgxz/t.txt' with binary;
默认为 TEXT 格式
使用 delimiter 指定列与列之间的分隔符
copy public.t to '/data/pgxz/t.txt' with delimiter '@';
cat /data/pgxz/t.txt
指定分隔文件各列的字符。文本格式中默认是一个制表符，而 CSV 格式中默认是一个逗号。分隔符必须是一个单一的单字节字符，即汉字是不支持的。使用 binary 格式时不允许这个选项。
NULL 值的处理
copy public.t to '/data/pgxz/t.txt' with NULL 'NULL';
cat /data/pgxz/t.txt
记录值为 NULL 时导出为 NULL 字符。使用 binary 格式时不允许这个选项。
生成列标题名
使用 CSV 格式和 HEADER 选项导出表：
```sql
copy public.t to '/data/pgxz/t.txt' with csv HEADER;
导出结果查看：
cat /data/pgxz/t.txt
f1,f2,f3,f4
1,Tbase,,7
2,pgxc,2017-10-28 18:24:05.643102,3
3,pgxz,2017-10-28 18:24:05.645691,
尝试在非 CSV 模式下使用 HEADER 选项会导致错误：
copy public.t to '/data/pgxz/t.txt' with HEADER;
ERROR: COPY HEADER available only in CSV mode
导出 oids 系统列
创建一个带有 oids 的新表：
drop table t;
CREATE TABLE t (
    f1 integer NOT NULL,
    f2 text NOT NULL,
    f3 timestamp without time zone,
    f4 integer
) with oids DISTRIBUTE BY SHARD (f1);
导入数据并包含 oids：
copy t from '/data/pgxz/t.txt' with csv;
查询确认导入结果：
select * from t;
f1
f2
f3
f4
1
Tbase
﻿
7
2
pg'", xc
2017-10-28 18:24:05.643102
3
3
pgxz
2017-10-28 18:24:05.645691
﻿
使用 oids 选项导出数据：
copy t to '/data/pgxz/t.txt' with oids;
导出结果查看：
cat /data/pgxz/t.txt
35055 1 Tbase \\N 7
35056 2 pg'", xc 2017-10-28 18:24:05.643102 3
35177 3 pgxz 2017-10-28 18:24:05.645691 \\N
使用 quote 自定义引用字符
默认 CSV 导出引用字符为双引号：
copy t to '/data/pgxz/t.txt' with csv;
导出结果查看：
cat /data/pgxz/t.txt
1,Tbase,,7
2,"pg'"", xc%",2017-10-28 18:24:05.643102,3
3,pgxz,2017-10-28 18:24:05.645691,
自定义引用字符为百分号：
copy t to '/data/pgxz/t.txt' with quote '%' csv;
导出结果查看：
cat /data/pgxz/t.txt
1,Tbase,,7
2,%pg'", xc%%%,2017-10-28 18:24:05.643102,3
3,pgxz,2017-10-28 18:24:05.645691,
使用 escape 自定义逃逸符
不指定 escape 时，默认与 QUOTE 值相同：
copy t to '/data/pgxz/t.txt' with quote '%' csv;
自定义 escape 为 '@'：
copy t to '/data/pgxz/t.txt' with quote '%' escape '@' csv;
导出结果查看：
cat /data/pgxz/t.txt
1,Tbase,,7
2,%pg'", xc@%%,2017-10-28 18:24:05.643102,3
3,pgxz,2017-10-28 18:24:05.645691,
尝试使用多字节字符作为 escape 会导致错误：
copy t to '/data/pgxz/t.txt' with quote '%' escape '@@' csv;
ERROR: COPY escape must be a single one-byte character
强制给某个列添加引用字符
使用 CSV 格式和 `force_quote` 选项导出指定列：
```sql
copy t to '/data/pgxz/t.txt' (format 'csv', force_quote (f1,f2));
导出结果查看：
cat /data/pgxz/t.txt
"1","Tbase",,7
"2","pg'"", xc%",2017-10-28 18:24:05.643102,3
"3","pgxz",2017-10-28 18:24:05.645691,
指定多列强制添加引用字符，列顺序可以任意排列：
copy t to '/data/pgxz/t.txt' (format 'csv', force_quote (f1,f4,f3,f2));
导出结果查看：
cat /data/pgxz/t.txt
"1","Tbase",,"7"
"2","pg'"", xc%","2017-10-28 18:24:05.643102","3"
"3","pgxz","2017-10-28 18:24:05.645691",
尝试在非 CSV 模式下使用 force_quote 选项会导致错误：
copy t to '/data/pgxz/t.txt' (format 'text', force_quote (f1,f2,f3,f4));
ERROR: COPY force quote available only in CSV mode
使用 encoding 指定导出文件内容编码
使用不同编码导出文件：
copy t to '/data/pgxz/t.csv' (encoding utf8);
copy t to '/data/pgxz/t.csv' (encoding gbk);
使用 set_client_encoding 设置客户端编码并导出 CSV 文件：
set client_encoding to gbk;
copy t to '/data/pgxz/t.csv' with csv;
使用多字节做分隔符
腾讯云数据仓库 TCHouse-P 5.06.2.1 以上版本支持多字节分隔符。
创建新表并插入数据：
create table t_position_copy(f1 int, f2 varchar(10), f3 int);
insert into t_position_copy values(123, '阿弟', 2021);
使用多字节分隔符导出数据：
copy t_position_copy to '/data/tbase/t_position_copy_mul_delimiter.text' with delimiter '@\\u0026';
导出结果查看：
cat /data/tbase/t_position_copy_mul_delimiter.text
123@\\u0026阿弟@\\u00262021
COPY FROM 用法详解--复制文件内容到数据表中
导入所有列
cat /data/pgxz/t.txt
truncate table t;
copy t from '/data/pgxz/t.txt';
select * from t;
导入部分指定列
将指定列的数据复制到文件：
```sql
copy t(f1,f2) to '/data/pgxz/t.txt';
查看文件内容：
cat /data/pgxz/t.txt
1 Tbase
2 pg'", xc%
3 pgxz
清空表 t 并从文件导入指定列：
truncate table t;
copy t(f1,f2) from '/data/pgxz/t.txt';
查询确认导入结果：
select * from t;
f1
f2
f3
f4
1
Tbase
2017-10-30 11:54:16.559579
﻿
2
pg'
2017-10-30 11:54:16.559579
﻿
3
pgxz
2017-10-30 11:54:16.560283
﻿
指定导入文件格式
导入文本文件：
copy t from '/data/pgxz/t.txt' (format 'text');
导入 CSV 文件：
copy t from '/data/pgxz/t.csv' (format 'csv');
导入二进制文件：
copy t from '/data/pgxz/t.bin' (format 'binary');
查询确认导入结果：
select * from t;
f1
f2
f3
f4
Tbase
﻿
﻿
7
pg'", xc%
2017-10-28 18:24:05.643102
3
﻿
pgxz
2017-10-28 18:24:05.645691
﻿
﻿
使用 delimiter 指定列与列之间的分隔符
使用自定义分隔符导入文本文件：
copy t from '/data/pgxz/t.txt' (format 'text', delimiter '%');
使用自定义分隔符导入 CSV 文件：
copy t from '/data/pgxz/t.csv' (format 'csv', delimiter '%');
查询确认导入结果：
select * from t;
f1
f2
f3
f4
1
Tbase
﻿
7
2
pg'", xc%
2017-10-28 18:24:05.643102
3
3
pgxz
2017-10-28 18:24:05.645691
﻿
NULL 值处理
导入文件中包含 NULL 字符串：
copy t from '/data/pgxz/t.txt' (null 'NULL');
查询确认 NULL 值处理结果：
select * from t;
f1
f2
f3
f4
1
Tbase
﻿
7
2
pg'", xc%
2017-10-28 18:24:05.643102
3
3
pgxz
2017-10-28 18:24:05.645691
﻿
注意：
字符串 NULL 在不同情境下可能不被识别，导致导入错误。
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"
自定义 quote 字符
查看 CSV 文件内容：
```shell
cat /data/pgxz/t.csv
1,Tbase,,7
2,%pg'", xc%%%,2017-10-28 18:24:05.643102,3
3,pgxz,2017-10-28 18:24:05.645691,
导入 CSV 文件时配置 quote 字符：
copy t from '/data/pgxz/t.csv' (format 'csv', quote '%');
自定义 escape 字符
查看 CSV 文件内容：
cat /data/pgxz/t.csv
1,Tbase,,7
2,"pg'@", xc%",2017-10-28 18:24:05.643102,3
3,pgxz,2017-10-28 18:24:05.645691,
导入 CSV 文件时配置 escape 字符：
copy t from '/data/pgxz/t.csv' (format 'csv', escape '@');
查询确认导入结果：
select * from t;
f1
f2
f3
f4
1
Tbase
﻿
7
2
pg'", xc%
2017-10-28 18:24:05.643102
3
3
pgxz
2017-10-28 18:24:05.645691
﻿
CSV Header 忽略首行
查看 CSV 文件内容（含头部）：
cat /data/pgxz/t.csv
f1,f2,f3,f4
1,Tbase,,7
2,"pg'"", xc%",2017-10-28 18:24:05.643102,3
3,pgxz,2017-10-28 18:24:05.645691,
导入 CSV 文件时忽略 header：
copy t from '/data/pgxz/t.csv' (format 'csv', header true);
查询确认导入结果：
select * from t;
f1
f2
f3
f4
Tbase
﻿
﻿
7
pg'", xc%
2017-10-28 18:24:05.643102
3
﻿
pgxz
2017-10-28 18:24:05.645691
﻿
﻿
导入 oid 列值
查看文本文件内容（含 oid）：
cat /data/pgxz/t.txt
35242 1 Tbase \\N 7
35243 2 pg'", xc% 2017-10-28 18:24:05.643102 3
35340 3 pgxz 2017-10-28 18:24:05.645691 \\N
导入文本文件并保留 oid：
truncate table t;
copy t from '/data/pgxz/t.txt' (oids true);
查询确认导入结果：
select oid, * from t;
oid
f1
f2
f3
f4
35242
1
Tbase
﻿
7
35243
2
pg'", xc%
2017-10-28 18:24:05.643102
3
35340
3
pgxz
2017-10-28 18:24:05.645691
﻿
使用 FORCE_NOT_NULL 把某列中空值变成长度为 0 的字符串
查看 CSV 文件内容（含空值）：
cat /data/pgxz/t.csv
1,Tbase,,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:14:14.954213,4
导入 CSV 文件使用 FORCE_NOT_NULL：
truncate table t;
copy t from '/data/pgxz/t.csv' (format 'csv', FORCE_NOT_NULL (f2));
查询确认某列空值处理结果：
select * from t where f2 = '';
f1
f2
f3
f4
4
﻿
2017-10-30 16:14:14.954213
4
Encoding 指定导入文件的编码
使用 `enca` 命令确定文件编码：
```shell
enca -L zh_CN /data/pgxz/t.txt
Simplified Chinese National Standard; GB2312
导入文件到数据库：
copy t from '/data/pgxz/t.txt';
查询确认导入结果：
select * from t;
f1
f2
f3
f4
1
Tbase
﻿
7
2
pg'", xc%
2017-10-28 18:24:05.643102
3
3
pgxz
2017-10-28 18:24:05.645691
﻿
不指定导入文件的编码格式，则无法正确导入中文字符。使用 GBK 编码重新导入：
truncate table t;
copy t from '/data/pgxz/t.txt' (encoding gbk);
再次查询确认导入结果：
select * from t;
f1
f2
f3
f4
1
Tbase
﻿
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
可以使用 enconv 命令转换文件编码后再导入数据：
truncate table t;
enconv -L zh_CN -x UTF-8 /data/pgxz/t.txt
copy t from '/data/pgxz/t.txt';
查询确认转换编码后的导入结果：
select * from t;
Position 指定字段长度导入
查看待导入的文本文件内容：
cat /data/tbase/t_position_copy.txt
123 阿弟 2021
使用 position 指定字段长度进行导入：
copy t_position_copy (f1 position(1:3), f2 position(4:9), f3 position(10:13)) from '/data/tbase/t_position_copy.txt';
position 指定字段长度和函数处理导入
```shell
cat /data/tbase/t_position_copy.txt
123 阿弟 2020010120200101121212
copy t_position_copy (f1 position(1:3), f2 position(4:9), f3 position(10:17) to_date($3,'yyyymmdd'), f4 position(18:31) to_timestamp($4,'yyyymmddhh24miss')) from '/data/tbase/t_position_copy.txt';
position 指定使用的编码
腾讯云数据仓库 TCHouse-P 5.06.2.1 以上版本支持，这个功能是用于要导入的数据文件的字符编码与数据库的编码不一致时能正常导入数据。
file /data/tbase/t_position_copy.txt
/data/tbase/t_position_copy.txt: ISO-8859 text
不指定编码导入数据时会出错：
copy t_position_copy (f1 position(1:3), f2 position(4:7), f3 position(8:11)) from '/data/tbase/t_position_copy.txt';
ERROR: multibyte character "Ü2" is truncated for column f2
CONTEXT: COPY t_position_copy, line 1: "123°¢µÜ2021", nodetype:1(1:cn,0:dn)
指定要导入的文件编码为 gbk：
copy t_position_copy (f1 position(1:3), f2 position(4:7), f3 position(8:11)) from '/data/tbase/t_position_copy.txt' encoding 'gbk';
select * from t_position_copy;
f1
f2
f3
123
阿弟
2021
注意，gbk 编码中一个汉字的长度是 2。
如果您的 gbk 文件是从 oracle 导出来的，则导入到 tbase 的 utf 数据库这样处理才能正常导入：
copy t4(f1 position(1:1), f2 position(2:3)) from '/data/tbase/t4.txt' with csv encoding 'gbk';
如果不指定，则导入遇到如“猏”，“玕”字会出错：
copy t4(f1 position(1:1), f2 position(2:3)) from '/data/tbase/t4.txt' encoding 'gbk';
ERROR: character with byte sequence 0xab 0x0a in encoding "GBK" has no equivalent in encoding "UTF8"
CONTEXT: COPY t4, line 1: "1«1ÌÚ", nodetype:1(1:cn,0:dn)
使用多字节分隔符
腾讯云数据仓库 TCHouse-P5.06.2.1 以上版本支持。
create table t_position_copy(f1 int, f2 varchar(10), f3 int);
cat /data/tbase/t_position_copy_mul_delimiter.text
123@\\u0026阿弟@\\u00262021
copy t_position_copy from '/data/tbase/t_position_copy_mul_delimiter.text' with delimiter '@\\u0026';
select * from t_position_copy;
f1
f2
f3
123
阿弟
2021
﻿

Column	Type	Modifiers	Storage	Stats target	Description
f1	integer	not null	plain
f2	character	not null	extended
f3	timestamp	default now()	plain
f4	integer		plain

f1	f2	f3	f4
3	pgxz	2017-10-28 18:24:05.645691
1	Tbase		7
2		2017-10-28 18:24:05.643102	3

f1	f2	f3	f4
1	Tbase		7
2	pg'", xc	2017-10-28 18:24:05.643102	3
3	pgxz	2017-10-28 18:24:05.645691

f1	f2	f3	f4
1	Tbase	2017-10-30 11:54:16.559579
2	pg'	2017-10-30 11:54:16.559579
3	pgxz	2017-10-30 11:54:16.560283

f1	f2	f3	f4
Tbase			7
pg'", xc%	2017-10-28 18:24:05.643102	3
pgxz	2017-10-28 18:24:05.645691

oid	f1	f2	f3	f4
35242	1	Tbase		7
35243	2	pg'", xc%	2017-10-28 18:24:05.643102	3
35340	3	pgxz	2017-10-28 18:24:05.645691

copy 的使用

本页目录：

实验表结构及数据

COPY TO 用法详解--复制数据到文件中

导出所有列

导出部分列

导出查询结果

指定生成文件格式

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

NULL 值的处理

生成列标题名

导出 oids 系统列

使用 quote 自定义引用字符

使用 escape 自定义逃逸符

强制给某个列添加引用字符

使用 encoding 指定导出文件内容编码

使用多字节做分隔符

COPY FROM 用法详解--复制文件内容到数据表中

导入所有列

导入部分指定列

指定导入文件格式

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

NULL 值处理

自定义 quote 字符

自定义 escape 字符

CSV Header 忽略首行

导入 oid 列值

使用 FORCE_NOT_NULL 把某列中空值变成长度为 0 的字符串

Encoding 指定导入文件的编码

Position 指定字段长度导入

position 指定字段长度和函数处理导入

position 指定使用的编码

使用多字节分隔符