PostgreSQL中的copy命令快速导入导出到平面文件CSV和TXT
在PostgreSQL中，利用copy命令快速导入导出数据到平面文件CSV和TXT1，利用copy to 命令导出表数据到CSV，copy from 命令从CSV文件中导入数据到表，可以自定义，换行符。
create table if not exists test_copy
(
c1 int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
c2 int,
c3 varchar(50),
c4 timestamp
);
create table if not exists test_copy2
(
c1 int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
c2 int,
c3 varchar(50),
c4 timestamp
);
insert into test_copy(c2,c3,c4)values(100,'abc',now());
insert into test_copy(c2,c3,c4)values(200,'def',now());
insert into test_copy(c2,c3,c4)values(null,'xxx',now());
insert into test_copy(c2,c3,c4)values(300, null,now());
insert into test_copy(c2,c3,c4)values(500, 'yyy',null);
insert into test_copy(c2,c3,c4)values(600, 'zzz',now());
select * from test_copy;
c1|c2 |c3 |c4 |
--+---+---+-----------------------+
1|100|abc|2025-08-04 15:51:58.388|
2|200|def|2025-08-04 15:52:11.673|
3| |xxx|2025-08-04 15:52:29.600|
4|300| |2025-08-04 15:52:45.656|
5|500|yyy| |
6|600|zzz|2025-08-04 15:53:33.747|
-- 导出，不指定分隔符的时，默认分隔符是","
COPY test_copy TO '/usr/local/pgsql16/backup/test_copy1.csv' WITH (FORMAT csv, HEADER true);
-- 导出，不指定分隔符的时，自定义分隔符
COPY test_copy TO '/usr/local/pgsql16/backup/test_copy2.csv' WITH (FORMAT csv, HEADER true, DELIMITER '|');
-- 导入,不指定分隔符的时，默认分隔符是","
COPY test_copy2 FROM '/usr/local/pgsql16/backup/test_copy1.csv' WITH (FORMAT csv, HEADER true);
-- 导入,不指定分隔符的时
COPY test_copy2 FROM '/usr/local/pgsql16/backup/test_copy2.csv' WITH (FORMAT csv, HEADER true, DELIMITER '|');
select * from test_copy2;
c1|c2 |c3 |c4 |
--+---+---+-----------------------+
1|100|abc|2025-08-04 15:51:58.388|
2|200|def|2025-08-04 15:52:11.673|
3| |xxx|2025-08-04 15:52:29.600|
4|300| |2025-08-04 15:52:45.656|
5|500|yyy| |
6|600|zzz|2025-08-04 15:53:33.747|
2，利用copy to 命令导出表数据到txt，copy from 命令从txt文件中导入数据到表，可以自定义，换行符，分隔符等信息。
-- 导出到txt，指定 NULL为'\N'
COPY test_copy TO '/usr/local/pgsql16/backup/test_copy1.txt' WITH (
FORMAT text,
DELIMITER E'\t', -- 换行符：Tab分隔符
NULL '\N', -- null值处理
ENCODING 'UTF8'
);
-- 导出到txt，指定 NULL为''，不建议
COPY test_copy TO '/usr/local/pgsql16/backup/test_copy2.txt' WITH (
FORMAT text,
DELIMITER E'\t', -- 换行符：Tab分隔符
NULL '', -- null值处理
ENCODING 'UTF8'
);
create table if not exists test_copy3
(
c1 int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
c2 int,
c3 varchar(50),
c4 timestamp
);
create table if not exists test_copy4
(
c1 int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
c2 int,
c3 varchar(50),
c4 timestamp
);
--导入txt
COPY test_copy3 from '/usr/local/pgsql16/backup/test_copy1.txt' WITH (
FORMAT text,
DELIMITER E'\t', -- 换行符：Tab分隔符
NULL '\N', -- null值处理
ENCODING 'UTF8'
);
select * from test_copy3;
c1|c2 |c3 |c4 |
--+---+---+-----------------------+
1|100|abc|2025-08-04 15:51:58.388|
2|200|def|2025-08-04 15:52:11.673|
3| |xxx|2025-08-04 15:52:29.600|
4|300| |2025-08-04 15:52:45.656|
5|500|yyy| |
6|600|zzz|2025-08-04 15:53:33.747|
COPY test_copy4 from '/usr/local/pgsql16/backup/test_copy2.txt' WITH (
FORMAT text,
DELIMITER E'\t', -- 换行符：Tab分隔符
NULL '', -- null值处理
ENCODING 'UTF8'
);
select * from test_copy4;
c1|c2 |c3 |c4 |
--+---+---+-----------------------+
1|100|abc|2025-08-04 15:51:58.388|
2|200|def|2025-08-04 15:52:11.673|
3| |xxx|2025-08-04 15:52:29.600|
4|300| |2025-08-04 15:52:45.656|
5|500|yyy| |
6|600|zzz|2025-08-04 15:53:33.747|
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！
页:
[1]