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

导入 CSV 文件

测试数据 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)

导出 CSV 文件

导出全部内容以及标题

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