postgresql 用双引号在 postgres 中输出到 CSV
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22078456/
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
Output to CSV in postgres with double-quotes
提问by Justin S
Trying to dump the output of a query into a CSV file in an automated job and running into an issue with fields where the column contains my comma delimiter. With the nature of this particular network, I have to jump through a couple of hoops to get things done, and there's a good chance I'm missing something very obvious.
尝试将查询的输出转储到自动化作业中的 CSV 文件中，并遇到列包含逗号分隔符的字段的问题。由于这个特定网络的性质，我必须跳过几个环节才能完成任务，而且很有可能我遗漏了一些非常明显的内容。
In a nutshell, I kick off my script from a client machine that uses PLINK to run a remote psql command on another box over an SSH connection. That psql command is hitting a Postgres server on a third machine (I can't connect directly from client to DB, hence the extra step in between).
简而言之，我从使用 PLINK 通过 SSH 连接在另一个机器上运行远程 psql 命令的客户端机器启动我的脚本。该 psql 命令正在访问第三台机器上的 Postgres 服务器（我无法直接从客户端连接到数据库，因此中间有额外的步骤）。
If I manually SSH from client to server 1, connect to the Postgres box, and use \copy... with CSV header, the file that's created is perfect, and any fields that contain a comma are automatically surrounded by double quotes.
如果我手动从客户端 SSH 连接到服务器 1，连接到 Postgres 框，并使用 \copy... 和 CSV 标头，则创建的文件是完美的，并且任何包含逗号的字段都会自动用双引号括起来。
However, if I try go issue that \copy (or copy) command in a single command, the output doesn't contain those double quotes, so I end up in that situation where commas in a field are interpreted as a delimiter later one.
但是，如果我尝试在单个命令中发出该 \copy（或复制）命令，则输出不包含那些双引号，因此我最终会遇到字段中的逗号被解释为稍后的分隔符的情况。
In other words, this has the necessary double-quotes:
换句话说，这有必要的双引号：
- SSH from client to server1.
- psql -Uuser -h server2 database
- \copy (select ...) to '~/myfile.csv' with CSV header;
- 从客户端到服务器 1 的 SSH。
- psql -Uuser -h server2 数据库
- \copy (select ...) to '~/myfile.csv' with CSV header;
But doesn't:
但不会：
- SSH from client to server1
psql -Uuser -h server2 database -c "\copy (select ...) to '~/myfile.csv' with CSV header;"
- 从客户端到服务器 1 的 SSH
psql -Uuser -h server2 database -c "\copy (select ...) to '~/myfile.csv' with CSV header;"
回答by Houari
Using FORCE_QUOTE
使用 FORCE_QUOTE
Here is how to do:
这是如何做的：
psql -U user -h server2 database -c "\copy (select ...) to '~/myfile.csv' WITH (FORMAT CSV, HEADER TRUE, FORCE_QUOTE *);"
COPYcommand documentation
COPY命令文档
