如何将表导出为带有 Postgresql 标题的 CSV？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1120109/
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
How to export table as CSV with headings on Postgresql?
提问by Elitmiar
I'm trying to export a PostgreSQL table with headings to a CSV file via command line, however I get it to export to CSV file, but without headings.
我正在尝试通过命令行将带有标题的 PostgreSQL 表导出到 CSV 文件，但是我将其导出到 CSV 文件，但没有标题。
My code looks as follows:
我的代码如下所示：
COPY products_273 to '/tmp/products_199.csv' delimiters',';
回答by Milen A. Radev
回答by Laurent Debricon
From psql command line:
从 psql 命令行：
\COPY my_table TO 'filename' CSV HEADER
no semi-colon at the end.
结尾没有分号。
回答by Dhruvil Thaker
instead of just table name, you can also write a query for getting only selected column data.
除了表名之外，您还可以编写一个查询来仅获取选定的列数据。
COPY (select id,name from tablename) TO 'filepath/aa.csv' DELIMITER ',' CSV HEADER;
with admin privilege
有管理员权限
\COPY (select id,name from tablename) TO 'filepath/aa.csv' DELIMITER ',' CSV HEADER;
回答by Brian
When I don't have permission to write a file out from Postgres I find that I can run the query from the command line.
当我无权从 Postgres 写出文件时，我发现我可以从命令行运行查询。
psql -U user -d db_name -c "Copy (Select * From foo_table LIMIT 10) To STDOUT With CSV HEADER DELIMITER ',';" > foo_data.csv
回答by jordg
This works
这有效
psql dbname -F , --no-align -c "SELECT * FROM TABLE"
回答by maytham-???????
For version 9.5 I use, it would be like this:
对于我使用的 9.5 版，它会是这样的：
COPY products_273 TO '/tmp/products_199.csv' WITH (FORMAT CSV, HEADER);
回答by Atihska
This solution worked for me using \copy.
这个解决方案适用于我使用\copy.
psql -h <host> -U <user> -d <dbname> -c "\copy <table_name> FROM '<path to csvfile/file.csv>' with (format csv,header true, delimiter ',');"
回答by Yan Foto
回答by Horse O'Houlihan
Heres how I got it working power shell using pgsl connnect to a Heroku PG database:
下面是我如何使用 pgsl 连接到 Heroku PG 数据库使其工作的 power shell：
I had to first change the client encoding to utf8 like this: \encoding UTF8
我必须首先将客户端编码更改为 utf8，如下所示： \encoding UTF8
Then dumped the data to a CSV file this:
然后将数据转储到 CSV 文件中：
\copy (SELECT * FROM my_table) TO C://wamp64/www/spider/chebi2/dump.csv CSV DELIMITER '~'
I used ~ as the delimiter because I don't like CSV files, I usually use TSV files, but it won't let me add '\t' as the delimiter, so I used ~because its a rarely used characeter.
我使用 ~ 作为分隔符，因为我不喜欢 CSV 文件，我通常使用 TSV 文件，但它不会让我添加 '\t' 作为分隔符，所以我使用~因为它是一个很少使用的字符。
回答by Mohit Singh
Try this: "COPY products_273 FROM '\tmp\products_199.csv' DELIMITER ',' CSV HEADER"
试试这个：“COPY products_273 FROM '\tmp\products_199.csv' DELIMITER ',' CSV HEADER”
