合并两列数据并添加到一个新列中。

Question

在PostgreSQL中，我想使用SQL语句将两个列合并并从它们创建一个新列。

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

- Rock

Answer 1

一般来说，我同意@kgrittn的建议。去试试吧。

但是，针对你的基本问题关于concat()：如果您需要处理null值 - 而且null既不在您的问题中也不在您所提到的问题中被排除掉，则它非常有用。

如果您可以排除null值，则老式（SQL标准）的连接操作符||仍然是最好的选择，@luis的答案很好：

SELECT col_a || col_b;

如果你的任何一列可以为空，在这种情况下结果将为null。你可以使用COALESCE来防范：

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

但是当有更多的参数时，这种方法很快变得烦琐。这就是 concat() 的用武之地，即使所有参数都为 null，它也不会返回 null。参考手册：

NULL 参数将被忽略。

SELECT concat(col_a, col_b);

对于两种选择，剩余的情况是当所有输入列都为null时，我们仍然会得到一个空字符串''。要获得null，请使用以下代码：

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()，但添加一个检查特殊条件的方法：

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) 的简写。只有当所有列都为null时，行类型才为null。详细解释请参见：

• 对一组列实施非空约束

此外，使用concat_ws()在元素之间添加分隔符（ws代表“带分隔符”）。

---

像Kevin答案中的表达式：

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

在没有使用concat()的情况下，为了处理PostgreSQL 8.3中的空值是很繁琐的。其中一种方法（有很多种）：

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, '');

函数的波动性只有稳定

concat()和concat_ws()是稳定函数，而不是IMMUTABLE，因为它们可以调用依赖于区域设置的数据类型输出函数(比如timestamptz_out)。
Tom Lane解释。

这禁止了它们在索引表达式中的直接使用。如果你知道在你的情况下结果实际上是不变的，你可以使用一个IMMUTABLE函数包装器来解决这个问题。例子在这里：

• PostgreSQL支持"忽略重音"吗？

- Erwin Brandstetter

---

不错！我之前没有注意到concat()函数与SQL标准的||连接运算符在处理NULL值时的差异。（你可能已经注意到了，我在我的答案中使用了NOT NULL列来回避这个问题。）现在我有了一个新工具可以使用了。 - kgrittn

希望我能选择多个被接受的答案。感谢您提供完整的解释。非常感谢所有的回答！ - Rock

哇，非常好的解释！感谢ROW（col_a，col_b），我以前从未听说过它，对于其他用途也非常方便。 - robotik

Answer 2

您不需要将列存储起来以便这样引用它。请尝试以下方法：

设置：

CREATE TABLE tbl
  (zipcode text NOT NULL, city text NOT NULL, state text NOT NULL);
INSERT INTO tbl VALUES ('10954', 'Nanuet', 'NY');

我们可以看到我们有“正确的东西”：

\pset border 2
SELECT * FROM tbl;

+---------+--------+-------+
| 邮政编码 |  城市  | 州    |
+---------+--------+-------+
| 10954   | 纳纽埃特 | 纽约州 |
+---------+--------+-------+

现在添加一个带有所需的“列名”的函数，该函数以表的记录类型作为其唯一参数：

CREATE FUNCTION combined(rec tbl)
  RETURNS text
  LANGUAGE SQL
AS $$
  SELECT $1.zipcode || ' - ' || $1.city || ', ' || $1.state;
$$;

这将创建一个函数，只要指定表名或别名，就可以像使用表列一样使用它：

SELECT *, tbl.combined FROM tbl;

这是因为PostgreSQL首先检查实际列是否存在，但如果找不到列，且标识符带有关系名称或别名，则会查找上述函数，并使用行作为其参数运行该函数，返回结果就像它是一列一样。 如果您想这样做，甚至可以在这样的“生成列”上进行索引。

由于您未在每行中使用重复的数据或触发所有插入和更新上的触发器，因此这通常比替代方法更快。

- kgrittn

---

“它以表的记录类型作为唯一参数”的意思是：在创建表之后，我们如何找到它呢？ - payne

表的记录类型通常与表名相同。请参考以下示例--特别是CREATE TABLE tbl和CREATE FUNCTION combined(rec tbl)。要获得确定性答案，请查找表的pg_class行的reltype列，并使用它来查找匹配的pg_type行。 - kgrittn

Answer 3

你有检查过字符串连接函数吗？类似这样：

update table_c set column_a = column_b || column_c 

应该能够工作。更多信息在这里

- luis