将Postgres中的列拆分为多行

Question

假设我有这样的一个表：

subject	flag
this is a test	2

其中subject是text类型，flag是int类型。我想将此表格转换为Postgres中的以下内容：

token	flag
this	2
is	2
a	2
test	2

是否有一种简单的方法来实现这个目标？

- mgoldwasser

Answer 1

在Postgres 9.3及以上版本中，请使用LATERAL连接。其基本形式如下：

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

FROM列表中的逗号（几乎）等同于CROSS JOIN，对于在FROM列表中返回集合的函数（SRF），自动假定LATERAL。为什么说是“几乎”？请参见以下链接：

• PostgreSQL查询中无效的FROM子句表项引用

派生表的别名“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;

最后一种方法是可以接受的，因为在Postgres 10中SELECT列表中的SRF已经过清理。参见：

• SELECT子句中多个返回集函数的预期行为是什么？

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

• LATERAL JOIN和PostgreSQL中的子查询有什么区别？

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

• 在文本字段中选择包含子字符串的行
• 带有元素编号的PostgreSQL unnest()

- Erwin Brandstetter

---

我对LATERAL联接和unnest()函数都不是很熟悉。您如何将其表达为LATERAL联接？ - Manngo

@Manngo：这是一个侧向连接，只是使用了简短的语法。冗长的等价语句为：SELECT * FROM tbl t CROSS JOIN LATERAL unnest(string_to_array(t.subject, ' ')) AS s(token); 在链接的答案中有详细的解释。 - Erwin Brandstetter

谢谢。我一直认为 … , … 语法是一个简单的交叉连接。 - Manngo

对于函数，它会自动使用 cross join lateral。 - Erwin Brandstetter

Answer 2

我认为不需要使用join，只需将unnest()函数与string_to_array()一起使用即可：

SELECT unnest(string_to_array(subject, ' ')) as "token", flag FROM test;

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

- Matt

Answer 3

使用正则表达式拆分到包括lateral join的表函数

SELECT s.token, flag
FROM   tbl t, regexp_split_to_table(t.subject, ' ') s(token)
WHERE  flag = 2;

请参考https://www.postgresql.org/docs/9.3/functions-string.html了解函数的详细信息。

- user27137772