自动用定界符连接所有列 [英] Automatically concatenate all columns with delimiter

问题描述

我有表 Customers：

i have table "Custommers" :

 ID   Code Name
 1    10   Marry 

我想要得到的结果是：

  1^10^Marry

但我不想像这样显式地命名每个列：

but I don't want to name each column explicitly like this:

select ID||^||Code||^Name 
from Customers

有什么建议吗？

推荐答案

关于您唯一的选择是：

COPY Customers TO stdout WITH DELIMITER '^' CSV;

这样应该可以帮助您，直到可以升级为止。您只能从（SELECT .. COPY 或 COPY 指定列的子集。 。）查询是否不想要整个表格，例如：

so that should help you out until you can upgrade. You can specify only a subset of columns to COPY or COPY from a (SELECT ...) query if you don't want the whole table, eg:

COPY Customers("ID") TO stdout WITH DELIMITER '^' CSV;

或

COPY (SELECT * FROM Customers WHERE "ID" <> 4) TO stdout WITH DELIMITER '^' CSV;

并非所有客户端驱动程序都支持 COPY 。例如，这将在 psql 中起作用，但不能在PgJDBC中起作用。一些驱动程序（例如PgJDBC）提供了自己的API，可让您间接使用 COPY 。有关使用 COPY 命令的信息，请参见驱动程序手册。

Not all client drivers support COPY. This will work from psql but not PgJDBC, for example. Some drivers (like PgJDBC) provide their own API that let you use COPY indirectly. See the manual for your driver for information on using the COPY command.

一个非常丑陋的选择需要 COPY 支持是：

A very ugly alternative that doesn't require COPY support is:

SELECT (regexp_matches(replace(Customers::text,',','^'), '\((.*)\)'))[1] FROM Customers;

但这会把 any 逗号变成 ^ ，因此，如果您的 Name 字段包含 Doe，John ，您将获得 Doe ^ John 作为输出-可能不是您想要的。 Pg将引用包含逗号的字段，但是替换将不遵守该引用。您可能可以提出一个 regexp_replace 表达式，只要您努力尝试，它就可以做到。您将不得不处理字符串中文字 的情况（输出结果为 ）也请参见此SQLFiddle示例。

but this will turn any comma into a ^, so if your Name field contains Doe, John you'll get Doe^ John as output - probably not what you want. Pg will quote fields that contain commas, but the replace won't respect that quoting. You might be able to come up with a regexp_replace expression that'll do it if you try hard enough; you'll have to handle the case of a literal " in the string (resulting in "" in the output) too. See this SQLFiddle example.

我原本以为您可能只是想摆脱 || 运算符，在这种情况下如果您不使用PostgreSQL的旧版本，将会有所帮助：

I originally thought you might've just been trying to get rid of the || operators, in which case this would've helped if you weren't using an such an old version of PostgreSQL:

SELECT concat_ws('^', "ID", "Code", "Name") FROM Customers;

此外，请注意COPY语法以上是过时的样式。现代PostgreSQL版本上的新代码应使用：

Also, note that the COPY syntax above is the obsolete style. New code on modern PostgreSQL versions should be using:

COPY Customers TO stdout WITH (FORMAT CSV, DELIMITER '^')

相反。这对于8.3版本不起作用，因为它太旧了。

instead. This will not work for you on 8.3, it's too old.

这篇关于自动用定界符连接所有列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！