在带有双引号的postgres中输出为CSV [英] Output to CSV in postgres with double-quotes

问题描述

尝试将查询的输出转储到自动作业中的CSV文件中，并遇到列中包含逗号分隔符的字段存在问题。有了这个特定的网络的性质，我必须跳过几个圈来完成的事情，有一个很好的机会，我错过了很明显的东西。

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.

简而言之，我从使用PLINK通过SSH连接在另一个框上运行远程psql命令的客户端计算机启动我的脚本。那个psql命令在第三台机器上命中一个Postgres服务器（我不能直接从客户端连接到DB，因此它之间的额外步骤）。

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).

如果我手动SSH从客户端到服务器1，连接到Postgres框，并使用\copy ...与CSV头，创建的文件是完美的，任何包含逗号的字段会自动包围双引号。

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.

但是，如果我尝试在一个命令中尝试\copy（或复制）命令，输出不包含那些双引号，所以我最终在那种情况下

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.

换句话说，这里有必要的双引号：

In other words, this has the necessary double-quotes:

1. 从客户端到服务器1的SSH。


2. psql -Uuser -h server2数据库


3. \copy选择...）到带有CSV标题的〜/ myfile.csv;

但不包括：

1. 从客户端到服务器1的SSH


2. psql -Uuser -h server2 database -c\copy 。）到带有CSV标题的〜/ myfile.csv;

推荐答案

如何做：

psql -U user -h server2 database -c "\copy (select ...) to '~/myfile.csv' WITH (FORMAT CSV, HEADER TRUE, FORCE_QUOTE *);

COPY 命令文档

这篇关于在带有双引号的postgres中输出为CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！