PostgreSQL:将结果数据从SQL查询导出到Excel / CSV [英] PostgreSQL: export resulting data from SQL query to Excel/CSV
问题描述
我需要将结果数据从PostgreSQL中的查询导出为Excel / CSV。
我使用 PostgreSQL 8.2.11
。
SQL错误:
错误:相对路径不允许COPY到文件
在声明中:
COPY(从m_product中选择不同的(m_price))到''c:\ auto_new.txt'';
Unix风格的文件名示例
COPY(SELECT * FROM tbl)TO'/var/lib/postgres/myfile1.csv'format csv;
阅读有关 COPY
的手册(链接到版本8.2)。
为目标文件使用绝对路径。一定要用空格加双引号文件名。示例为MS Windows:
COPY(SELECT * FROM tbl)
TO E'C:\\ Documents and Settings \\\\ Tech\Desktop\\myfile1.csv'format csv;
在 PostgreSQL 8.2 中, standard_conforming_strings = off
默认情况下,您需要加倍反斜杠,因为 \
是一个特殊字符,由PostgreSQL解释。适用于任何版本。 精美的手册:
文件名
$ b输入或输出文件的路径名称。 Windows用户可能需要使用
E'
字符串和双反斜杠作为路径分隔符。
或使用 standard_conforming_strings = on
(默认自Postgres 9.1):
COPY tbl - - (SELECT * FROM tbl)
TO'C:\Documents and Settings\Tech\Desktop\myfile1.csv'(format csv);
或者您可以也可以在Windows下使用正斜杠作为文件名。
另一种方法是使用<元指令 \copy
的默认终端客户端 psql
。
您也可以使用像 pgadmin ,然后从结果网格复制/粘贴到Excel中进行小型查询。
密切相关的答案: I need to export the resulting data from a query in PostgreSQL to Excel/CSV.
Example with Unix-style file name: Read the manual about In PostgreSQL 8.2, with filename The absolute path name of the input or output file. Windows users might need to use an Or the modern syntax with Or you can also use forward slashes for filenames under Windows. An alternative is to use the meta-command You can also use a GUI like pgadmin and copy / paste from the result grid to Excel for small queries. Closely related answer: Similar solution for MySQL: 这篇关于PostgreSQL:将结果数据从SQL查询导出到Excel / CSV的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
$ b MySQL的类似解决方案:
I use 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"';
COPY (SELECT * FROM tbl) TO '/var/lib/postgres/myfile1.csv' format csv;
COPY
(link to version 8.2).
You have to use an absolute path for the target file. Be sure to double quote file names with spaces. Example for MS Windows:COPY (SELECT * FROM tbl)
TO E'"C:\\Documents and Settings\\Tech\Desktop\\myfile1.csv"' format csv;
standard_conforming_strings = off
per 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:
E''
string and double backslashes used as path separators.standard_conforming_strings = on
(default since Postgres 9.1):COPY tbl -- short for (SELECT * FROM tbl)
TO '"C:\Documents and Settings\Tech\Desktop\myfile1.csv"' (format csv);
\copy
of the default terminal client psql
.