copy使用
更新時間 2025-02-05 09:37:09
最近更新時間: 2025-02-05 09:37:09
分享文章
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 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)
