合并两列并添加到新列中 [英] Combine two columns and add into one new column

问题描述

在PostgreSQL中，我想使用一条SQL语句来合并两列并根据它们创建一个新列.

In PostgreSQL, I want to use an SQL statement to combine two columns and create a new column from them.

我正在考虑使用concat(...)，但是还有更好的方法吗?
最好的方法是什么?

I'm thinking about using concat(...), but is there a better way?
What's the best way to do this?

推荐答案

通常，我同意 @kgrittn的建议.去吧.

但是要解决关于 的基本问题> concat() :如果您需要处理 null值，并且您的问题或查询中都没有排除null，则新功能concat()非常有用.您所指的一个.

But to address your basic question about concat(): The new function concat() is useful if you need to deal with null values - and null has neither been ruled out in your question nor in the one you refer to.

如果，您可以排除空值，那么旧的(SQL标准)串联运算符 @ luis的答案很好:

If you can rule out null values, the good old (SQL standard) concatenation operator || is still the best choice, and @luis' answer is just fine:

SELECT col_a || col_b;

如果您的任一列都可以为空，那么在这种情况下，结果将为空.您可以使用COALESCE进行防御:

If either of your columns can be null, the result would be null in that case. You could defend with COALESCE:

SELECT COALESCE(col_a, '') || COALESCE(col_b, '');

但是随着更多的争论，这很快就变得乏味.这就是concat()进入的地方，即使 all 参数为null，从不返回null. 每个文档:

But that get tedious quickly with more arguments. That's where concat() comes in, which never returns null, not even if all arguments are null. Per documentation:

NULL参数将被忽略.

NULL arguments are ignored.

SELECT concat(col_a, col_b);

两种选择的剩余角情况是 所有输入列为空的情况，在这种情况下，我们仍然得到一个空字符串'' ，但有人可能希望将其改为null(至少我会这样).一种可能的方式:

The remaining corner case for both alternatives is where all input columns are null in which case we still get an empty string '', but one might want null instead (at least I would). One possible way:

SELECT CASE
          WHEN col_a IS NULL THEN col_b
          WHEN col_b IS NULL THEN col_a
          ELSE col_a || col_b
       END;

随着越来越多的列迅速变得更加复杂.再次，使用concat()，但是添加特殊条件的检查:

This gets more complex with more columns quickly. Again, use concat() but add a check for the special condition:

SELECT CASE WHEN (col_a, col_b) IS NULL THEN NULL
            ELSE concat(col_a, col_b) END;

这是如何工作的?
(col_a, col_b)是行类型表达式ROW (col_a, col_b)的简写形式.只有 all 列为空时，行类型才为空.详细说明:

How does this work?
(col_a, col_b) is shorthand notation for a row type expression ROW (col_a, col_b). And a row type is only null if all columns are null. Detailed explanation:

• 在一组列上的非空约束

此外，请使用 concat_ws() 在元素之间添加分隔符(ws表示带有分隔符").

Also, use concat_ws() to add separators between elements (ws for "with separator").

类似凯文回答中的表达:

An expression like the one in Kevin's answer:

SELECT $1.zipcode || ' - ' || $1.city || ', ' || $1.state;

在PostgreSQL 8.3(没有concat())中为空值做准备很繁琐.一种(许多)方式:

is tedious to prepare for null values in PostgreSQL 8.3 (without concat()). One way (of many):

SELECT COALESCE(
         CASE
            WHEN $1.zipcode IS NULL THEN $1.city
            WHEN $1.city    IS NULL THEN $1.zipcode
            ELSE $1.zipcode || ' - ' || $1.city
         END, '')
       || COALESCE(', ' || $1.state, '');

功能波动仅STABLE

concat()和concat_ws()是STABLE函数，而不是IMMUTABLE，因为它们可以调用依赖于语言环境设置的数据类型输出函数(例如timestamptz_out).

Function volatility is only STABLE

concat() and concat_ws() are STABLE functions, not IMMUTABLE because they can invoke datatype output functions (like timestamptz_out) that depend on locale settings.
Explanation by Tom Lane.

这禁止它们在索引表达式中直接使用.如果您知道在您的情况下结果实际上是不可变的，则可以使用IMMUTABLE函数包装器解决此问题.此处的示例:

This prohibits their direct use in index expressions. If you know that the result is actually immutable in your case, you can work around this with an IMMUTABLE function wrapper. Example here:

• PostgreSQL是否支持不区分重音"?排序规则?

这篇关于合并两列并添加到新列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！