postgresql 在 postgres 上将表导出到 csv
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20678664/
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
export table to csv on postgres
提问by Luba Weissmann
How can I export a table to .csvin Postgres, when I'm not superuser and can't use the copycommand?
.csv当我不是超级用户并且无法使用该copy命令时，如何将表导出到Postgres 中？
I can still import the data to postgres with "import" button on the right click, but no export option.
我仍然可以通过右键单击“导入”按钮将数据导入 postgres，但没有导出选项。
回答by marvinorez
Use psql and redirect stream to file:
使用 psql 并将流重定向到文件：
psql -U <USER> -d <DB_NAME> -c "COPY <YOUR_TABLE> TO stdout DELIMITER ',' CSV HEADER;" > file.csv
回答by Thusitha Sumanadasa
回答by user2553863
Besides what marvinorez's suggests in his answeryou can do, from psql:
除了 marvinorez 在他的回答中提出的建议之外，您还可以从psql：
\copy your_table TO '/path/to/your/file.csv' DELIMITER ',' CSV HEADER
On the other hand, from pgadmin3, you can also open the table by right clicking on it's name and then selecting View Data. Then you can click on the upper-left corner of the table (where the column name row joins with the row number column, a gray empty square) to select all rows. Finally, you can copy with CtrlCor Edit -> Copy in the menu. The data will be copied to the clipboard in csv format, delimited by semicolon ;.
You can then paste it in LibreOffice Calc, MS Excel to display for instance.
另一方面，从pgadmin3，您还可以通过右键单击表的名称然后选择查看数据来打开表。然后你可以点击表格的左上角（列名行与行号列连接的地方，一个灰色的空方块）来选择所有行。最后，您可以CtrlC在菜单中使用或 Edit -> Copy 进行复制。数据将以 csv 格式复制到剪贴板，以分号分隔;。然后，您可以将其粘贴到 LibreOffice Calc、MS Excel 中以进行显示。
If your table is large (what is large depends on the amount of RAM of your machine, among other things) it might not fit in the clipboard, so in that case, I would not use this method but the first one (\copy).
如果你的表很大（多大取决于你机器的 RAM 量，等等）它可能不适合剪贴板，所以在这种情况下，我不会使用这个方法，而是第一个 (\copy) .
回答by Jesse
I was having trouble with superuser and running psql, I took the simple stupid way using PGAdmin III.
1) SELECT * FROM ;
Before running select Query in the menu bar and select 'Query to File'
This will save it to a folder of your choice. May have to play with the settings on how to export, it likes quoting and ;.
2) SELECT * FROM ;
run normally and then save the output by selecting export in the File menu. This will save as a .csv
我在使用超级用户和运行 psql 时遇到了麻烦，我采用了使用 PGAdmin III 的简单愚蠢的方法。
1) 选择 * 从;
在运行之前在菜单栏中选择查询并选择“查询到文件”这会将其保存到您选择的文件夹中。可能必须使用有关如何导出的设置，它喜欢引用和 ;。
2) 选择 * 从;
正常运行，然后通过在“文件”菜单中选择“导出”来保存输出。这将另存为 .csv
This is not a good approach for large tables. Tables I have done this for are a few 100,000 rows and 10-30 columns. Large tables may have problems.
对于大表来说，这不是一个好方法。我这样做的表是几个 100,000 行和 10-30 列。大表可能有问题。
回答by Draugr
The easiest way would indeed be a COPY to stdout I think. If you can't do this, how about using pg_dump and then transform the output file with sed, AWK or even a text editor? This should work even with search and replace in an acceptable amount of time :)
我认为最简单的方法确实是复制到标准输出。如果你不能这样做，如何使用 pg_dump 然后用 sed、AWK 甚至文本编辑器转换输出文件？即使在可接受的时间内进行搜索和替换，这也应该有效:)
