将PostgreSQL中PL/pgSQL的输出保存为CSV文件

Question

将PostgreSQL中PL/pgSQL的输出保存为CSV文件

sqlpostgresqlcsvpostgresql-copy

1114

什么是将PL/pgSQL输出保存到CSV文件的最简单方法？

我使用带有pgAdmin III和PSQL插件的PostgreSQL 8.4运行查询。

- Hoff

1

请参见https://dev59.com/_nNA5IYBdhLWcg3wBpDs - Peter Krauss

21个回答

636

有几种解决方案：

1 `psql` 命令

psql -d 数据库名 -t -A -F"," -c "select * from users" > 输出.csv

这个方法的优点是可以通过 SSH 使用，例如 ssh postgres@主机名命令 - 让您轻松获取。

2 postgres `copy` 命令

COPY (SELECT * from users) To '/tmp/output.csv' With CSV;

3 psql 交互式（或非交互式）

>psql dbname
psql>\f ','
psql>\a
psql>\o '/tmp/output.csv'
psql>SELECT * from users;
psql>\q

所有这些都可以在脚本中使用，但我更喜欢＃1。

4 pgadmin 但这不能脚本化。

- sorin

37

个人认为，第一种选项容易出错，因为它在导出数据时没有包含适当的逗号转义。 - Piohen

4

此外，psql不会给单元格的值加上引号，因此如果你的任何数据使用了分隔符，你的文件就会出现损坏。 - Cerin

7

@Cerin -t 是 --tuples-only 的同义词（关闭打印列名和结果行数底部等内容），如果不使用它，则会得到列标题。 - ic3b3rg

25

刚刚测试了逗号转义的说法，方法1确实不会在数值中转义逗号。 - MrColes

1

可以使用一条psql命令来代替查询：select * from users使用每个varchar列的引用函数：quote_literal()：select id, quote_literal(name), quote_literal(email), ... from users - Dek4nice

显示剩余12条评论

109

在终端中（连接到数据库时），将输出设置为csv文件。

1）将字段分隔符设置为 ',':

\f ','

2) 将输出格式设置为不对齐：

\a

3) 仅显示元组：

\t

4) 设置输出：

\o '/tmp/yourOutputFile.csv'

5) 执行您的查询：

:select * from YOUR_TABLE

6) 输出：

\o

然后您就可以在这个位置找到您的csv文件:

cd /tmp

使用 scp 命令进行拷贝，或使用 nano 进行编辑：

nano /tmp/yourOutputFile.csv

- Marcin Wasiluk

4

并且 \o 为了再次打印控制台 - metdos

2

这不会生成CSV文件，它只会将命令输出记录到文本文件中（这并不意味着它是逗号分隔的）。 - Ruslan Kabalin

@RuslanKabalin 是的，我刚刚注意到了，并修改了指令以创建逗号分隔的输出（CSV）。 - Marcin Wasiluk

5

我会改进这个答案，指出“csv”输出不会被正确转义，并且每次执行SQL命令时，结果都会连接到输出文件中。 - Danny Armstrong

字段值中的换行符怎么办？COPY或\copy方法可以正确处理（转换为标准CSV格式）；这个方法呢？ - Wildcard

@MarcinWasiluk 请查看这个评论。您可以将\t替换为\pset footer以实现相同的目标，并保留标题的附加值。我尝试自己编辑您的答案，但被拒绝了。 - EliadL

66

CSV导出统一

这些信息并没有很好地呈现出来。由于这是我第二次需要推导出它，所以我将把它放在这里，以便提醒自己。

实际上，获取Postgres中的CSV最佳方法是使用COPY ... TO STDOUT命令。虽然您不想按照此处答案中显示的方式执行操作。使用该命令的正确方式如下：

COPY (select id, name from groups) TO STDOUT WITH CSV HEADER

只需记住一个命令！

在ssh上使用非常方便：

$ ssh psqlserver.example.com 'psql -d mydb "COPY (select id, name from groups) TO STDOUT WITH CSV HEADER"' > groups.csv

它非常适用于通过ssh在docker内部使用:

$ ssh pgserver.example.com 'docker exec -tu postgres postgres psql -d mydb -c "COPY groups TO STDOUT WITH CSV HEADER"' > groups.csv

即使在本地机器上，它也表现非常出色：

$ psql -d mydb -c 'COPY groups TO STDOUT WITH CSV HEADER' > groups.csv

在本地机器上使用 Docker 还是直接运行？

docker exec -tu postgres postgres psql -d mydb -c 'COPY groups TO STDOUT WITH CSV HEADER' > groups.csv

或者在 Kubernetes 集群中，使用 Docker，通过 HTTPS 连接？

kubectl exec -t postgres-2592991581-ws2td 'psql -d mydb -c "COPY groups TO STDOUT WITH CSV HEADER"' > groups.csv

如此多才多艺，逗号用得好！

你真的会吗？

是的，我知道，这是我的笔记：

COPYses

使用/copy可以在执行psql命令的系统上有效地执行文件操作，作为执行它的用户1。如果连接到远程服务器，则可以简单地将数据文件从执行psql的系统复制到/从远程服务器。

COPY以后端进程用户帐户（默认为postgres）在服务器上执行文件操作，检查并相应地应用文件路径和权限。如果使用TO STDOUT，则绕过文件权限检查。

这两个选项都需要随后移动文件，如果psql未在您希望结果CSV最终驻留的系统上执行，则最有可能出现这种情况。根据我的经验，当您主要与远程服务器一起工作时，情况就是这样。

配置类似于通过ssh建立到远程系统的TCP/IP隧道以进行简单的CSV输出更加复杂，但对于其他输出格式（二进制），可能更好地通过隧道连接上的/copy，执行本地psql。同样，在进行大量导入时，将源文件移动到服务器并使用COPY可能是最高性能的选项。

PSQL参数

使用psql参数可以像CSV格式一样格式化输出，但存在缺点，例如必须记住禁用分页器并且无法获取标题：

$ psql -P pager=off -d mydb -t -A -F',' -c 'select * from groups;'
2,Technician,Test 2,,,t,,0,,                                                                                                                                                                   
3,Truck,1,2017-10-02,,t,,0,,                                                                                                                                                                   
4,Truck,2,2017-10-02,,t,,0,,

其他工具

不，我只想从我的服务器中获取CSV文件，而无需编译或安装任何工具。

- joshperry

2

结果保存在哪里？我的查询已经运行了，但是文件没有出现在我的电脑上。这是我正在做的事情：COPY（select a，b from c where d ='1'）TO STDOUT WITH CSVHEADER > abcd.csv - kRazzy R

1

@kRazzyR 输出结果会发送到 psql 命令的标准输出，所以最终数据会被发送到标准输出。在我的示例中，我使用“> file.csv”将其重定向到文件。您需要确保这在通过 psql -c 参数发送到服务器的命令之外。请参见“本地机器”示例。 - joshperry

1

感谢完整的解释。用psql进行复制命令实在是太复杂了。我通常使用免费的数据库客户端（dbeaver社区版）来导入和导出数据文件。它提供了很好的映射和格式化工具。您的答案为从远程系统复制提供了很好的详细示例。 - Rich Lysakowski PhD

1

这是一个惊人的解决方案。非常感谢。 - harryghgim

49

新版本 - psql 12 - 将支持 --csv。

psql - devel

--csv

切换到 CSV（逗号分隔值）输出模式，等同于\pset format csv。

csv_fieldsep

指定在 CSV 输出格式中使用的字段分隔符。如果分隔符字符出现在字段的值中，则按照标准 CSV 规则在双引号内输出该字段。默认值为逗号。

用法：

psql -c "SELECT * FROM pg_catalog.pg_tables" --csv  postgres

psql -c "SELECT * FROM pg_catalog.pg_tables" --csv -P csv_fieldsep='^'  postgres

psql -c "SELECT * FROM pg_catalog.pg_tables" --csv  postgres > output.csv

- Lukasz Szozda

40

如果您对特定表的所有列及其标题感兴趣，可以使用以下方法：

COPY table TO '/some_destdir/mycsv.csv' WITH CSV HEADER;

这比那个稍微简单一点点

COPY (SELECT * FROM table) TO '/some_destdir/mycsv.csv' WITH CSV HEADER;

据我所知，这两者是等价的。

- benjwadams

1

如果查询是自定义的（即具有列别名或连接不同的表），则标题将打印出与屏幕上显示的列别名相同的内容。 - Devy

28

我不得不使用\COPY命令，因为我收到了错误消息：

ERROR:  could not open file "/filepath/places.csv" for writing: Permission denied

所以我使用了：

\Copy (Select address, zip  From manjadata) To '/filepath/places.csv' With CSV;

并且它正在运作

- maudulus

2

我也遇到了权限被拒绝的错误。通过先发送到 /tmp 文件夹来解决它。例如：\copy (SELECT * FROM messages) TO '/tmp/messages.csv' With CSV HEADER; - Somto

24

我正在使用AWS Redshift，它不支持COPY TO功能。

我的BI工具支持制表符分隔的CSV文件，因此我使用了以下方法：

 psql -h dblocation -p port -U user -d dbname -F $'\t' --no-align -c "SELECT * FROM TABLE" > outfile.csv

- calcsam

太好了，谢谢！我使用 psql -h dblocation -p port -U user -d dbname -F $',' --no-align -c "SELECT * FROM TABLE" > outfile.csv 来获取CSV文件。虽然没有对字段进行引用，但对于我的目的来说已经足够了。 - Lightheaded

你可以配置 .pg_service.conf 文件来将连接参数别名化，例如 psql service=default -F $'\t' ...。 - combinatorist

Redshift支持UNLOAD命令。 - Himanshu

23

psql可以为您完成此操作：

edd@ron:~$ psql -d beancounter -t -A -F"," \
                -c "select date, symbol, day_close " \
                   "from stockprices where symbol like 'I%' " \
                   "and date >= '2009-10-02'"
2009-10-02,IBM,119.02
2009-10-02,IEF,92.77
2009-10-02,IEV,37.05
2009-10-02,IJH,66.18
2009-10-02,IJR,50.33
2009-10-02,ILF,42.24
2009-10-02,INTC,18.97
2009-10-02,IP,21.39
edd@ron:~$

请参考man psql获取有关此处使用的选项的帮助信息。

- Dirk Eddelbuettel

13

如果数据中含有逗号，这不是一个真正的 CSV 文件，所以最好使用内置的 COPY 支持。但是，这种通用技术对于从 Postgres 中快速导出除 CSV 外的其他分隔格式非常方便。 - Greg Smith

13

在pgAdmin III中，可以从查询窗口导出到文件。在主菜单中选择Query -> Execute to file或者使用相同功能的按钮（一个绿色三角形和一个蓝色软盘，而不是普通的绿色三角形只运行查询）。如果您不是从查询窗口运行查询，则建议按照IMSoP的建议使用复制命令。

- Amanda Nyren

IMSoP的答案对我没有用，因为我需要成为超级管理员。这个方法非常有效。谢谢！ - Mike Neumegen

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- IMSoP · Accepted Answer

你想要将生成的文件放在服务器上还是客户端上？

服务器端

如果您想要易于重用或自动化的内容，可以使用Postgresql内置的COPY命令。例如：

Copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',' HEADER;

这种方法完全在远程服务器上运行，不能写入本地 PC。它还需要作为 Postgres "superuser"（通常称为 "root"）运行，因为 Postgres 无法阻止其对该机器的本地文件系统进行恶意操作。

这并不意味着您必须连接为超级用户（自动化将是不同类型的安全风险），因为您可以使用SECURITY DEFINER选项创建函数，使函数以超级用户一样运行。

关键部分是您的函数执行其他检查，而不仅仅是绕过安全检查 - 因此，您可以编写一个导出所需数据的函数，或者编写一个可以接受各种选项的内容，只要它们符合严格的白名单。您需要检查两件事:

1. 用户应允许读/写哪些文件？这可能是特定目录，例如，文件名可能必须具有适当的前缀或扩展名。 2. 用户应在数据库中读取/写入哪些表？这通常由数据库中的GRANT定义，但是函数现在正在作为超级用户运行，因此通常“禁止”的表将完全可访问。您可能不希望让某个人调用您的函数并在您的“用户”表末尾添加行……

我已经写了一篇博客文章扩展这种方法,包括一些函数导出（或导入）符合严格条件的文件和表格的示例。

客户端

另一种方法是在应用程序或脚本中在客户端处理文件操作。Postgres服务器不需要知道您要复制到哪个文件，它只会输出数据，客户端将其放在某个位置。

此操作的底层语法是COPY TO STDOUT命令，图形化工具如pgAdmin会为您包装成一个漂亮的对话框。

psql命令行客户端有一个特殊的“元命令”叫做\copy，它接受与“真正”的COPY相同的所有选项，但在客户端中运行：

\copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',' HEADER

请注意，没有终止符;，因为元命令由换行符终止，而不是SQL命令。

来自文档：

不要将COPY与psql指令\copy混淆。 \copy调用COPY FROM STDIN或COPY TO STDOUT，然后将数据提取/存储在对psql客户端可访问的文件中。因此，在使用\copy时，文件可访问性和访问权限取决于客户机而不是服务器。

您的应用程序编程语言可能还支持推送或获取数据，但通常无法在标准SQL语句中使用COPY FROM STDIN/TO STDOUT，因为没有连接输入/输出流的方法。 PHP的PostgreSQL处理程序（不是 PDO）包括非常基本的pg_copy_from和pg_copy_to函数，可复制到/从PHP数组，这对于大型数据集可能不是有效的。

将PostgreSQL中PL/pgSQL的输出保存为CSV文件

服务器端

客户端

1 psql 命令

2 postgres copy 命令

3 psql 交互式（或非交互式）

4 pgadmin 但这不能脚本化。

CSV导出统一

只需记住一个命令！

你真的会吗？

COPYses

PSQL参数

其他工具

1 `psql` 命令

2 postgres `copy` 命令