PostgreSQL 的 COPY
语句可以完成导入和导出 CSV 文件的功能(需要相关权限), 此外 psql 的 \copy
命令通过运行 COPY 语句也可实现类似的功能
创建一个测试表 tokyo2020
CREATE TABLE tokyo2020 (rank integer, team varchar(32), gold integer, silver integer, copper integer);
测试表的结构如下:
psql (12.12 (Ubuntu 12.12-0ubuntu0.20.04.1))
Type "help" for help.
postgres=> CREATE TABLE tokyo2020 (rank integer, team varchar(32), gold integer, silver integer, copper integer);
CREATE TABLE
postgres=> \d tokyo2020
Table "public.tokyo2020"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
rank | integer | | |
team | character varying(32) | | |
gold | integer | | |
silver | integer | | |
copper | integer | | |
测试数据 tokyo2020.csv, 其中第一行为标题
rank,team,gold,silver,copper
1,America,39,41,33
2,China,38,32,18
3,Japan,27,14,17
4,Britain,22,21,22
5,Russia,20,28,23
在 psql 中的导入命令为:
COPY tokyo2020 FROM '/mnt/g/tokyo2020.csv' WITH CSV HEADER;
-- 或者
\copy tokyo2020 from '/mnt/g/tokyo2020.csv' with csv header;
WITH CSV HEADER
指定文件格式为 CSV 且首行为标题
使用 COPY 语句导入时必须使用管理员账户或者具有 pg_read_server_files 权限的用户, 而 \copy 命令不受此影响
使用管理员账户给当前用户授予权限
# 以 postgres 的身份运行 psql(sudo 默认以 root 身份运行)
$ sudo -u postgres psql
postgres=# GRANT pg_read_server_files TO jianghuixin;
之后便可执行 COPY 语句
postgres=> COPY tokyo2020 FROM '/mnt/g/tokyo2020.csv' WITH CSV HEADER;
COPY 5
修改测试数据 tokyo2020.csv, 首行不再是标题, 分隔符变成了 ‘|’
1|America|39|41|33
2|China|38|32|18
3|Japan|27|14|17
4|Britain|22|21|22
5|Russia|20|28|23
修改后 psql 的导入命令为:
COPY tokyo2020 FROM '/mnt/g/tokyo2020.csv' WITH CSV DELIMITER '|';
-- 或者
\copy tokyo2020 from '/mnt/g/tokyo2020.csv' with csv delimiter '|';
通过 DELIMITER 自定义分隔符
重新测试导入
postgres=> TRUNCATE TABLE tokyo2020;
TRUNCATE TABLE
postgres=> \copy tokyo2020 from '/mnt/g/tokyo2020.csv' with csv delimiter '|';
COPY 5
如果表结构中包含 id 字段, 而 CSV 文件中没有 id 这一列, 就需要在导入时指定部分列
重新创建 tokyo2020 表, 新增 id 字段
postgres=> DROP TABLE tokyo2020;
DROP TABLE
postgres=> CREATE TABLE tokyo2020 (id serial primary key, rank integer, team varchar(32), gold integer, silver integer, copper integer);
CREATE TABLE
postgres=> \d tokyo2020
Table "public.tokyo2020"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------------------------------------
id | integer | | not null | nextval('tokyo2020_id_seq'::regclass)
rank | integer | | |
team | character varying(32) | | |
gold | integer | | |
silver | integer | | |
copper | integer | | |
Indexes:
"tokyo2020_pkey" PRIMARY KEY, btree (id)
对于包含 id 的表, 由于 CSV 数据一般不包含 id 序号, 导入时需要忽略 id 这一列, 由 PostgreSQL 自动生成 id 值
指定除 id 以外所有列的命令为:
COPY tokyo2020(rank, team, gold, silver, copper) FROM '/mnt/g/tokyo2020.csv' with CSV DELIMITER '|';
-- 或者
\copy tokyo2020(rank, team, gold, silver, copper) from '/mnt/g/tokyo2020.csv' with csv delimiter '|';
导入结果:
postgres=> \copy tokyo2020(rank, team, gold, silver, copper) from '/mnt/g/tokyo2020.csv' with csv delimiter '|';
COPY 5
postgres=> SELECT * FROM tokyo2020;
id | rank | team | gold | silver | copper
----+------+---------+------+--------+--------
1 | 1 | America | 39 | 41 | 33
2 | 2 | China | 38 | 32 | 18
3 | 3 | Japan | 27 | 14 | 17
4 | 4 | Britain | 22 | 21 | 22
5 | 5 | Russia | 20 | 28 | 23
(5 rows)
导出全部内容以及标题
COPY tokyo2020 to '~/tokyo2020.csv' WITH CSV HEADER;
-- 或者
\copy tokyo2020 to '~/tokyo2020.csv' with csv header;
对于 COPY
语句, 当前用户需要 pg_write_server_files
权限
# 以 postgres 的身份运行 psql
$ sudo -u postgres psql
postgres=# GRANT pg_write_server_files TO jianghuixin;
postgres=> \copy tokyo2020 to '~/tokyo2020.csv' with csv header;
COPY 5
生成文件的内容为:
id,rank,team,gold,silver,copper
1,1,America,39,41,33
2,2,China,38,32,18
3,3,Japan,27,14,17
4,4,Britain,22,21,22
5,5,Russia,20,28,23
导出部分字段(忽略 id 列), 分隔符使用 ‘|’, 并且不要标题
postgres=> \copy tokyo2020(rank,team,gold,silver,copper) to '~/tokyo2020.csv' with csv delimiter '|';
COPY 5
生成 tokyo2020.csv 的内容
1|America|39|41|33
2|China|38|32|18
3|Japan|27|14|17
4|Britain|22|21|22
5|Russia|20|28|23
PostgreSQL 支持导出 SELECT 语句的查询结果
postgres=> SELECT team, gold+silver+copper AS medal FROM tokyo2020;
team | medal
---------+-------
America | 113
China | 88
Japan | 58
Britain | 65
Russia | 71
(5 rows)
postgres=> \copy (SELECT team, gold+silver+copper AS medal FROM tokyo2020) to '~/tokyo2020.csv' with csv header;
COPY 5
生成 tokyo2020.csv 的内容
team,medal
America,113
China,88
Japan,58
Britain,65
Russia,71