如何将 PostgreSQL 查询结果导出为 csv 文件

Question

我遇到了一个问题，无法将我的PostgreSQL输出从shell导出为csv文件。
我的SQL脚本名为script.sql。

我在shell中输入了以下命令：

psql congress -af script.sql &> filename.csv

但是当我打开filename.csv文件时，Excel csv中所有列的值都被挤压到一个列中（请参见附带的屏幕截图）。

然后我尝试了另一种方式。我编辑了我的script.sql：

Copy (Select * From ...) To '/tmp/filename.csv' With CSV;

然后我在数据库的 shell 中输入了以下命令：dbname。

\i script.sql

输出结果为：

COPY 162

我的输出查询有162行。

所以我输出表格的162行已经被复制到了shell中。我该如何将它们粘贴或移动到csv文件中呢？

或者，如果我要使用filename.csv（见附图），我该如何修复那个csv/Excel文件的格式呢？

- mflowww

---

当您在Excel或其他类似程序中打开文件时，请将分隔符更改为“|”。 - cur4so

https://dev59.com/cmsz5IYBdhLWcg3wFUC_ - user330315

请查看此处更全面的答案，其中澄清了在 psql 中 \copy（客户端）与 COPY（服务器端）查询的区别：https://dev59.com/q3I_5IYBdhLWcg3wHvQ5 - nealmcb

Answer 1

现代语法：

COPY (SELECT * FROM ...) TO '/tmp/filename.csv' (FORMAT csv);

所以我的输出表格有162行已经复制到了shell中。我该如何将它们粘贴或移动到csv文件中？结果就是CSV文件。使用匹配分隔符的任何电子表格程序打开它手册：。默认格式为文本格式的制表符，CSV格式的逗号。psql元命令\copy是SQL COPY函数的包装器。它写入和读取客户端本地文件（而COPY使用服务器本地文件），并且不需要超级用户特权。请参阅：

• 从PostgreSQL表中导出特定行作为INSERT SQL脚本
• PostgreSQL：将SQL查询结果导出到Excel / CSV

- Erwin Brandstetter

---

COPY命令需要超级用户权限才能执行，而普通用户可以使用\copy命令，并且语法与COPY相同。 - Patrick

这会忽略默认输出中的头部吗？ - Spike

@Spike 是的。如果您想要标题行，请使用（格式CSV，HEADER）。 - HelLViS69

Answer 2

大多数之前的回答是正确的，这里提供更多详细信息，仅适用于Linux。

1. 创建一个名为my_query.sql的文件。

\COPY (select * from some_table) TO '/tmp/some_file.csv' (format CSV);

在更新数据库和文件路径变量后，请运行以下命令。

注意：保留HTML标记。

psql "host=YOUR_HOST port=YOUR_PORT dbname=YOUR_DB user=YOUR_USER password=YOUR_PASS" -af my_query.sql

提示：要小心你可用的空闲磁盘空间，如果你执行 "select * from your_table"，并且你的表很大，你可能会因为磁盘空间不足而运行失败。

- grepit

Answer 3

首先将你的连接信息复制到~/.pgpass文件中，然后

cat ip:port:dbname:user:pass > ~/.pgpass
chmod 0600 ~/.pgpass
psql -h serverip -U userid dbname -af test.sql | gzip > result.txt.gz

- Ash

Answer 4

稍微离题一下，还有另外一种方法。

我在Windows批处理脚本中使用以下内容：

psql.exe -U %cUser% -h %cHost% -p %cPort% -d %cDB% -t -o "%dumpDir%\tables.txt" -A -c "SELECT table_schema, table_name   FROM information_schema.tables WHERE table_schema = '%qSchema%';"

诀窍是记住-A选项。它会抑制数据的空格填充。

我这样做是为了避免COPY命令显示的权限错误，因为运行postgress的帐户没有与运行我的定期批处理文件的帐户相同的权限。

这使我得到一个给定模式中表的列表，结果如下：

myschema|mytable1

myschema|mytable2

然后我使用FOR批处理命令来处理每一行。如果你真的想要一个CSV文件，你只需要这样做：

ECHO table_schema,table_name > %dumpDir%\tables.csv
FOR /F "delims=|" %%p in (%dumpDir%\tables.txt) DO echo %%p,%%q >> %dumpDir%\tables.csv

这可能不是最高效的机制，但在少量输出行上运行良好。

- Darren Gill

---

我喜欢使用 psql -c 'select * from table limit 5' -tAF 命令，它会自动产生 csv 格式的输出。 - benjimin

在哪里使用密码 - RU Ahmed

除非您的数据中包含 |，否则祝您好运。 - boatcoder