合并两列并添加到一个新列中 [英] 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 的建议.去吧.
Generally, I agree with @kgrittn's advice. Go for it.
但是为了解决您关于的基本问题concat():如果您需要处理空值，新函数concat() 很有用 -并且您的问题和您提到的问题中都没有排除 null.
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() 的用武之地，它 从不 返回 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:
这篇关于合并两列并添加到一个新列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
