在 Postgres 中将列拆分为多行
假设我有一张这样的表:
Suppose I have a table like this:
subject | flag
----------------+------
this is a test | 2
subject 是 text 类型，flag 是 int 类型.我想在 Postgres 中将此表转换为类似的内容:
subject is of type text, and flag is of type int. I would like to transform this table to something like this in Postgres:
token | flag
----------------+------
this | 2
is | 2
a | 2
test | 2
有没有简单的方法可以做到这一点?
Is there an easy way to do this?
在 Postgres 9.3+ 中使用 LATERAL join:
In Postgres 9.3+ use a LATERAL join:
SELECT s.token, flag
FROM tbl t, unnest(string_to_array(t.subject, ' ')) s(token)
WHERE flag = 2;
这是一个隐式的 LATERAL 连接.如果 unnest() 不返回任何行(空或 NULL subject)，结果将根本没有行.使用 LEFT JOIN unnest(...) i ON true 总是从 tbl 返回行.见:
It's an implicit LATERAL join. If unnest() does not return any rows (empty or NULL subject), the result will be no row at all. Use LEFT JOIN unnest(...) i ON true to always return rows from tbl. See:
您也可以使用 regexp_split_to_table()，但这通常会更慢，因为正则表达式匹配的成本更高.相关:
You could also use regexp_split_to_table(), but that's typically slower because regular expression matching costs a bit more. Related: