sql - 在 Postgres 中将列拆分为多行

Question

假设我有一个这样的表:

    subject     | flag
----------------+------
 this is a test |    2

subject 是text 类型，flag 是int 类型。我想在 Postgres 中将此表转换为类似这样的内容:

    token       | flag
----------------+------
 this           |    2
 is             |    2
 a              |    2
 test           |    2

有没有简单的方法来做到这一点？

Answer 1

在 Postgres 9.3+ 中使用 LATERAL 连接。最小形式:

SELECT token, flag
FROM   tbl, unnest(string_to_array(subject, ' ')) token
WHERE  flag = 2;

FROM 列表中的逗号(几乎)等同于CROSS JOIN，LATERAL 自动假定用于集合返回函数(SRF ) 在 FROM 列表中。为什么是“差不多”？见:

• "invalid reference to FROM-clause entry for table" in Postgres query

派生表的别名“token”也被假定为单个匿名列的列别名，并且我们在整个查询中假定了不同的列名。等效的，更冗长且不易出错:

SELECT s.token, t.flag
FROM   tbl t
CROSS  JOIN LATERAL unnest(string_to_array(subject, ' ')) AS s(token)
WHERE  t.flag = 2;

或者将 SRF 移动到 SELECT 列表，这在 Postgres 中是允许的(但在标准 SQL 中是不允许的)，以达到相同的效果:

SELECT unnest(string_to_array(subject, ' ')) AS token, flag
FROM   tbl
WHERE  flag = 2;

最后一个似乎可以接受，因为 SELECT 列表中的 SRF 已在 Postgres 10 中被清除。参见:

• What is the expected behaviour for multiple set-returning functions in SELECT clause?

如果 unnest() 没有返回任何行(空或 NULL subject)，(隐式)连接将从结果中删除该行。使用 LEFT JOIN ... ON true 保留来自 tbl 的符合条件的行。见:

• What is the difference between LATERAL JOIN and a subquery in PostgreSQL?

我们也可以使用 regexp_split_to_table() ，但这通常较慢，因为正则表达式的成本更高一些。见:

• SQL select rows containing substring in text field
• PostgreSQL unnest() with element number