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

Question

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

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

Answer 1

import json
cursor = conn.cursor()
qry = """ SELECT details FROM test_csvfile """ 
cursor.execute(qry)
rows = cursor.fetchall()

value = json.dumps(rows)

with open("/home/asha/Desktop/Income_output.json","w+") as f:
    f.write(value)
print 'Saved to File Successfully'

Answer 2

JackDB（网络浏览器中的数据库客户端）使这一切变得非常简单。特别是如果您使用 Heroku。

它允许您连接到远程数据库并对其运行 SQL 查询。

                    sp;                     sp;                     sp;                     sp;                     sp;                     sp; ^来源

_{（来源：jackdb.com)}

---

连接数据库后，您可以运行查询并导出到 CSV 或 TXT（请参见右下角）。

---

^{注意： 我与 JackDB 没有任何关系。我目前使用他们的免费服务，并认为这是一个很棒的产品。}

Answer 3

根据 @skeller88 的要求，我将我的评论重新发布为答案，这样那些没有阅读每条回复的人就不会迷失它......

DataGrip 的问题在于它会控制你的钱包。它不是免费的。请尝试 dbeaver.io 上的 DBeaver 社区版。它是一款面向 SQL 程序员、DBA 和分析师的 FOSS 多平台数据库工具，支持所有流行的数据库：MySQL、PostgreSQL、SQLite、Oracle、DB2、SQL Server、Sybase、MS Access、Teradata、Firebird、Hive、Presto 等。

DBeaver 社区版使得连接数据库、发出查询来检索数据，然后下载结果集以将其保存为 CSV、JSON、SQL 或其他常见数据格式变得非常简单。它是 TOAD for Postgres、TOAD for SQL Server 或 Toad for Oracle 的可行的 FOSS 竞争对手。

我与 DBeaver 没有任何关系。我喜欢它的价格和功能，但我希望他们能够更多地开放 DBeaver/Eclipse 应用程序，并使向 DBeaver/Eclipse 添加分析小部件变得容易，而不是要求用户支付年度订阅费用来直接在其中创建图形和图表该应用程序。我的 Java 编码技能已经生疏，我不想花几周时间重新学习如何构建 Eclipse 小部件，却发现 DBeaver 已经禁用了向 DBeaver 社区版添加第三方小部件的功能。

DBeaver 用户是否了解创建分析小部件以添加到 DBeaver 社区版中的步骤？

Answer 4

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

服务器端

如果你想要一些易于重用或自动化的东西，你可以使用 Postgresql 的内置 复制 命令。例如

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

这种方法完全在远程服务器上运行 - 它无法写入您的本地PC。它还需要作为 Postgres“超级用户”（通常称为“root”）运行，因为 Postgres 无法阻止它对该计算机的本地文件系统做一些令人讨厌的事情。

这实际上并不意味着您必须作为超级用户进行连接（自动化这将带来另一种安全风险），因为您可以使用 CREATE FUNCTION 的 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 时，文件可访问性和访问权限取决于客户端而不是服务器。

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

Answer 5

解决办法有以下几种：

1 psql 命令

psql -d dbname -t -A -F"," -c "select * from users" > output.csv

这有一个很大的优点，您可以通过 SSH 使用它，例如 ssh postgres@host 命令 - 使您能够获得

2 个 postgres copy 命令

< code>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 但这不可编写脚本。

Answer 6

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

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

Answer 7

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

它非常适合在 docker 内部通过 ssh 使用：

$ 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

如此多才多艺，很多逗号！

你甚至吗？

是的，我做到了，这是我的注释：

使用 /copy 的COPYses

可以在运行 psql 命令的任何系统上有效地执行文件操作，就像执行该命令的用户< a href="https://wiki.postgresql.org/wiki/COPY" rel="noreferrer">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，而不需要编译和/或安装工具。

Answer 8

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

psql - 开发

--csv

切换到 CSV（逗号分隔值）输出模式。这相当于 \pset 格式 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

Answer 9

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

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

简单一点，是等效的。

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

这比据我所知

Answer 10

我必须使用 \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;

并且它正在运行

Answer 11

我正在使用 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

Answer 12

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 以获取有关此处使用的选项的帮助。

Answer 13

在 pgAdmin III 中，有一个选项可以从查询窗口导出到文件。在主菜单中是“查询”->“查询”。执行到文件或者有一个按钮可以执行相同的操作（它是带有蓝色软盘的绿色三角形，而不是仅运行查询的纯绿色三角形）。如果您没有从查询窗口运行查询，那么我会执行 IMSoP 建议的操作并使用复制命令。

Answer 14

如果您有更长的查询并且喜欢使用 psql，则将查询放入文件并使用以下命令：

psql -d my_db_name -t -A -F";" -f input-file.sql -o output-file.csv

Answer 15

我编写了一个名为 psql2csv 的小工具，它封装了 COPY查询 TO STDOUT 模式，生成正确的 CSV。它的界面类似于psql。

psql2csv [OPTIONS] < QUERY
psql2csv [OPTIONS] QUERY

假定查询是 STDIN 的内容（如果存在）或最后一个参数。除以下参数外，所有其他参数都转发到 psql：

-h, --help           show help, then exit
--encoding=ENCODING  use a different encoding than UTF8 (Excel likes LATIN1)
--no-header          do not output a header

Answer 16

我尝试了几种方法，但很少有人能够为我提供所需的带有标题详细信息的 CSV。

这对我有用。

psql -d dbame -U username \
  -c "COPY ( SELECT * FROM TABLE ) TO STDOUT WITH CSV HEADER " > \
  OUTPUT_CSV_FILE.csv

Answer 17

从 Postgres 12 开始，您可以更改输出格式：

\pset format csv

允许使用以下格式：

aligned, asciidoc, csv, html, latex, latex-longtable, troff-ms, unaligned, wrapped

如果您想导出请求的结果，可以使用 \o filename 功能。

例子 ：

\pset format csv

\o file.csv
SELECT * FROM table LIMIT 10;
\o

\pset format aligned

Answer 18

要下载列名称为 HEADER 的 CSV 文件，请使用以下命令：

Copy (Select * From tableName) To '/tmp/fileName.csv' With CSV HEADER;

Answer 19

我发现 psql --csv 创建了一个包含 UTF8 字符的 CSV 文件，但缺少 UTF8 字节顺序标记 (0xEF 0xBB 0xBF)。如果不考虑这一点，此 CSV 文件的默认导入将损坏国际字符，例如 CJK 字符。

为了解决这个问题，我设计了以下脚本：

# Define a connection to the Postgres database through environment variables
export PGHOST=your.pg.host
export PGPORT=5432
export PGDATABASE=your_pg_database
export PGUSER=your_pg_user

# Place credentials in $HOME/.pgpass with the format:
# ${PGHOST}:${PGPORT}:${PGUSER}:master:${PGPASSWORD}

# Populate long SQL query in a text file:
cat > /tmp/query.sql <<EOF
SELECT item.item_no,item_descrip,
invoice.invoice_no,invoice.sold_qty
FROM item
LEFT JOIN invoice
ON item.item_no=invoice.item_no;
EOF

# Generate CSV report with UTF8 BOM mark
printf '\xEF\xBB\xBF' > report.csv
psql -f /tmp/query.sql --csv | tee -a report.csv

通过这种方式，我可以编写 CSV 创建过程的脚本以实现自动化，并允许我在单个源文件中简洁地维护脚本。

Answer 20

当您的查询太长且无法内联写入时，可以使用如下临时表：

CREATE TABLE tmp_table as (

    SELECT *
    FROM my_table mt
    WHERE ...

);

\COPY tmp_table TO '~/Desktop/tmp_table.csv' DELIMITER ';' CSV HEADER;
DROP TABLE tmp_table;

Answer 21

如果您使用的是 AWS，例如 AWS RDS，则可以使用 PGAdmin。我所做的是创建一个具有所需输出的临时表：

CREATE TABLE export_descriptions AS SELECT description FROM products WHERE id = 406;

然后在 PGAdmin 中，它有一个导出到 CSV 的选项：

在那里，您可以指定保存位置以及格式：

然后它会直接保存到您的计算机上。请记住，AWS RDS 向您隐藏了其运行的底层计算，因此您无权访问底层服务器（EC2 或 Fargate 实例）。换句话说，您无法通过 ssh 访问它。不过，您可以访问 postgres cli 并从 PGAdmin 连接到它，并且使用新的 PGAdmin 界面，可以轻松导出到 csv。