将带有数组的关系展平以每个数组条目发出一行

Question

假设有一个被定义为以下结构的表格：

CREATE TABLE test_values(name TEXT, values INTEGER[]);

...以及以下数值：

| name  | values  |
+-------+---------+
| hello | {1,2,3} |
| world | {4,5,6} |

我正在尝试找到一个能返回以下结果的查询：

| name  | value |
+-------+-------+
| hello | 1     |
| hello | 2     |
| hello | 3     |
| world | 4     |
| world | 5     |
| world | 6     |

我已经查阅了关于访问数组的上游文档，并尝试考虑使用unnest() 函数的解决方案会是什么样子，但是一直没有想到。

一个理想的解决方案应该易于使用，即使在除被扩展的数组之外还有大量列和没有主键的情况下也是如此。处理多个数组的情况并不重要。

- Charles Duffy

---

select name, unnest(values) "value" from test_values; 这个语句不起作用吗？ - bgoldst

Answer 1

我们可以将返回集合的函数 unnest() 放入 SELECT 列表中，就像Raphaël所建议的那样。在Postgres 10之前，这曾经出现过一些特殊情况的问题。请参见：

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

自从Postgres 9.3以来，我们也可以使用 LATERAL 连接进行操作。这是更加干净、符合标准的方式，将返回集合的函数放入 FROM 列表中，而不是放入 SELECT 列表中：

SELECT name, value
FROM   tbl, unnest(values) value;  -- implicit CROSS JOIN LATERAL

一个微妙的区别：这个查询会从结果中删除空/NULL值的行，因为unnest()没有返回任何行，而在FROM列表中相同的内容被转换为NULL值并被返回。100%等效的查询是：

SELECT t.name, v.value
FROM   tbl t
LEFT   JOIN unnest(t.values) v(value) ON true;

请参见:

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

- Erwin Brandstetter

---

谢谢你的好回答。我有一个快速的问题。最后一个查询中的v(value)是什么意思？我知道v是别名，但我以前从未见过这种表示法。再次感谢你的回答。 - George Pipis

Answer 2

好的，你提供数据和文档，那么我们就把它们混合在一起吧 ;)

select 
 name, 
 unnest(values) as value 
from test_values

请查看SqlFiddle

- Raphaël Althaus

---

对于使用序列作为其元素之一选择元组的语义不太熟悉，我表示无法辩解。当计时器结束时，我会接受它。 - Charles Duffy