PostgreSQL：将结果数据从 SQL 查询导出到 Excel/CSV
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8119297/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL: export resulting data from SQL query to Excel/CSV
提问by Ghostman
I need to export the resulting data from a query in PostgreSQL to Excel/CSV.
I use PostgreSQL 8.2.11.
我需要将结果数据从 PostgreSQL 中的查询导出到 Excel/CSV。
我用PostgreSQL 8.2.11.
SQL error:
ERROR: relative path not allowed for COPY to file
In statement:
COPY (select distinct(m_price) from m_product)TO '"c:\auto_new.txt"';
回答by Erwin Brandstetter
Example with Unix-style file name:
带有 Unix 样式文件名的示例：
COPY (SELECT * FROM tbl) TO '/var/lib/postgres/myfile1.csv' format csv;
Read the manual about COPY(link to version 8.2).
You have to use an absolute pathfor the target file. Be sure to double quote file names with spaces. Example for MS Windows:
阅读有关COPY（链接到版本 8.2）的手册。
您必须为目标文件使用绝对路径。确保双引号包含空格的文件名。MS Windows 示例：
COPY (SELECT * FROM tbl)
TO E'"C:\Documents and Settings\Tech\Desktop\myfile1.csv"' format csv;
In PostgreSQL 8.2, with standard_conforming_strings = offper default, you need to double backslashes, because \is a special character and interpreted by PostgreSQL. Works in any version. It's all in the fine manual:
在PostgreSQL 8.2 中，standard_conforming_strings = off默认情况下，您需要双反斜杠，因为它\是一个特殊字符并由 PostgreSQL 解释。适用于任何版本。这一切都在精美的手册中：
filename
The absolute path name of the input or output file. Windows users might need to use an
E''string and double backslashes used as path separators.
文档名称
输入或输出文件的绝对路径名。Windows 用户可能需要使用
E''字符串和双反斜杠作为路径分隔符。
Or the modern syntax with standard_conforming_strings = on(default since Postgres 9.1):
或现代语法standard_conforming_strings = on（自 Postgres 9.1 起默认）：
COPY tbl -- short for (SELECT * FROM tbl)
TO '"C:\Documents and Settings\Tech\Desktop\myfile1.csv"' (format csv);
Or you can also use forward slashesfor filenames under Windows.
或者您也可以在 Windows 下对文件名使用正斜杠。
An alternative is to use the meta-command \copyof the default terminal client psql.
另一种方法是使用默认终端客户端的元命令\copypsql。
You can also use a GUI like pgadminand copy / paste from the result grid to Excel for small queries.
您还可以使用pgadmin 之类的 GUI ，并将结果网格中的复制/粘贴到 Excel 以进行小型查询。
Closely related answer:
密切相关的答案：
Similar solution for MySQL:
MySQL的类似解决方案：
回答by Revol89
In PostgreSQL 9.4to create to file CSV with the header in Ubuntu:
在PostgreSQL 9.4 中，在Ubuntu 中创建带有标题的 CSV 文件：
COPY (SELECT * FROM tbl) TO '/home/user/Desktop/result_sql.csv' WITH CSV HEADER;
Note: The folder must be writable.
注意：文件夹必须是可写的。
回答by Celia
This worked for me:
这对我有用：
COPY (SELECT * FROM table)
TO E'C:\Program Files (x86)\PostgreSQL\8.4\data\try.csv';
In my case the problem was with the writing permission to a special folder (though I work as administrator), after changing the path to the original data folder under PostgreSQL I had success.
在我的情况下，问题在于对特殊文件夹的写入权限（尽管我以管理员身份工作），在更改 PostgreSQL 下原始数据文件夹的路径后，我成功了。
回答by a_horse_with_no_name
Several GUI tools like Squirrel, SQL Workbench/J, AnySQL, ExecuteQuery can export to Excel files.
Squirrel、SQL Workbench/J、AnySQL、ExecuteQuery 等多个 GUI 工具可以导出到 Excel 文件。
Most of those tools are listed in the PostgreSQL wiki:
大多数这些工具都列在 PostgreSQL wiki 中：
http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools
http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools
回答by Michael
If you have error like "ERROR: could not open server file "/file": Permission denied" you can fix it that:
如果您有类似“错误：无法打开服务器文件“/文件”：权限被拒绝”之类的错误，您可以修复它：
Ran through the same problem, and this is the solution I found: Create a new folder (for instance, tmp) under /home $ cd /home make postgres the owner of that folder $ chown -R postgres:postgres tmp copy in tmp the files you want to write into the database, and make sure they also are owned by postgres. That's it. You should be in business after that.
遇到了同样的问题，这是我找到的解决方案：在 /home $ cd /home 下创建一个新文件夹（例如，tmp）使 postgres 成为该文件夹的所有者 $ chown -R postgres:postgres tmp copy in tmp the要写入数据库的文件，并确保它们也归 postgres 所有。就是这样。在那之后你应该做生意。
回答by Michael
The correct script for postgres (Ubuntu) is:
postgres (Ubuntu) 的正确脚本是：
COPY (SELECT * FROM tbl) TO '/var/lib/postgres/myfile1.csv';
