如何在Postgres SELECT中连接列?

I have two string columns a and b.

我有两列a和b。

So select a,b from foo returns values a and b. However, concatenation of a and b does not work. I tried :

因此，从foo中选择a,b返回值a和b。但是，将a和b连接起来是行不通的。我试着:

select a || b from foo

and

和

select  a||', '||b from foo

OP's error message (from comments):

OP的错误消息(来自评论):

No operator matches the given name and argument type(s). You might need to add explicit type casts.

没有操作符匹配给定的名称和参数类型(s)。您可能需要添加显式类型转换。

He also stated that both fields are character(2).

他还指出，这两个字段都是字符(2)。

6 个解决方案

#1

Whatever the actual data types behind the curtain are, you can "fix" your statement by casting to text. Any type can be cast to text:

无论幕后的实际数据类型是什么，您都可以通过对文本的强制转换来“修复”语句。任何类型都可以转换为文本:

SELECT a::text || ', ' || b::text AS ab FROM foo;

If NULL values can be involved, use concat_ws() to avoid NULL results for one or more NULL arguments (Postgres 9.1 or later):

如果可以包含空值，使用concat_ws()避免一个或多个空参数的空结果(postgres9.1或更高):

SELECT concat_ws(', ', a::text, b::text) AS ab FROM foo;

Or just concat() if you don't need separators:

或者concat()，如果你不需要隔板:

SELECT concat(a::text, b::text) AS ab FROM foo;

More details (and why COALESCE is a poor substitute) in this related answer:

在这个相关的答案中有更多的细节(以及为什么COALESCE是一个糟糕的替代品):

• Combine two columns and add into one new column
• 合并两个列并添加到一个新的列中

Regarding update in the comment

+ is not a valid operator for string concatenation in Postgres (or the SQL standard). It's a private idea of Microsoft to add this to their products.

在Postgres(或SQL标准)中，+不是字符串连接的有效操作符。这是微软在其产品中加入的一个私人想法。

There is hardly any good reason to use character(n) (synonym: char(n) ). Use text or varchar. Details:

几乎没有什么好的理由使用字符(n)(同义词:char(n)))。使用文本或varchar。细节:

• Any downsides of using data type "text" for storing strings?
• 使用数据类型“文本”来存储字符串有什么缺点吗?
• Best way to check for "empty or null value"
• 检查“空值或空值”的最佳方法

Either way, the statement would just work in modern day Postgres with any character type. Try:

无论哪种方式，该语句都只适用于任何字符类型的现代Postgres。试一试:

SELECT 'a '::character(2) || 'b '::character(2);

You must be involving other data types.

您必须涉及其他数据类型。

#2

The problem was in nulls in the values; then the concatenation does not work with nulls. The solution is as follows:

问题是值的空值;然后连接对null就不起作用了。解决办法如下:

SELECT coalesce(a, '') || coalesce(b, '') FROM foo;

#3

it is better to use CONCAT function in PostgreSQL for concatenation

最好在PostgreSQL中使用CONCAT函数进行连接

eg : select CONCAT(first_name,last_name) from person where pid = 136

从pid = 136的person中选择CONCAT(first_name,last_name)

if you are using column_a || ' ' || column_b for concatenation for 2 column , if any of the value in column_a or column_b is null query will return null value. which may not be preferred in all cases.. so instead of this

如果您使用column_a || ' ' ' || column_b进行2列的连接，如果column_a或column_b中的任何值为null查询将返回null值。在所有情况下，这可能不是首选。而不是这个

||

| |

use

使用

CONCAT

CONCAT

it will return relevant value if either of them have value

如果它们中的任何一个都有值，它将返回相关的值

#4

CONCAT functions sometimes not work with older postgreSQL version

CONCAT函数有时不能使用旧的postgreSQL版本

see what I used to solve problem without using CONCAT

看看我以前不用CONCAT解决问题的方法

 u.first_name || ' ' || u.last_name as user,

Or also you can use

或者你也可以用。

 "first_name" || ' ' || "last_name" as user,

in second case I used double quotes for first_name and last_name

在第二种情况下，我对first_name和last_name使用了双引号

Hope this will be useful, thanks

希望这个有用，谢谢

#5

Try this

试试这个

select textcat(textcat(FirstName,' '),LastName) AS Name from person;

#6

For example if there is employee table which consists of columns as:

例如，如果有包含列的employee表，如:

employee_number,f_name,l_name,email_id,phone_number 

if we want to concatenate f_name + l_name as name.

如果我们想把f_name + l_name合并为name。

SELECT employee_number,f_name ::TEXT ||','|| l_name::TEXT  AS "NAME",email_id,phone_number,designation FROM EMPLOYEE;