PostgreSQL的：压扁的关系用一个阵列发射每个阵列入门一行 [英] PostgreSQL: Flattening a relation with an array to emit one row per array entry

问题描述

鉴于定义为这样的表：

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

...和以下值：

...and the following values:

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

我试图找到一个查询将返回：

I'm trying to find a query which will return:

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

我在访问阵列上游文档一>，并试图去思考什么用的在 UNNEST（）函数会是什么样子，但已经快到了空。

I've reviewed the upstream documentation on accessing arrays, and tried to think about what a solution using the unnest() function would look like, but have been coming up empty.

这是理想的解决方案将是很容易，即使在有正在扩大一个显著数比数组其它列的和没有主键的情况下使用。处理的情况下具有一个以上的阵列并不重要。

An ideal solution would be easy to use even in cases where there were a significant number of columns other than the array being expanded and no primary key. Handling a case with more than one array is not important.

推荐答案

您的可以的把设定返回函数 UNNEST（）进入 SELECT 像拉斐尔列表建议。但在Postgres的9.3或更高版本使用<一个href=\"http://www.postgresql.org/docs/current/interactive/queries-table-ex$p$pssions.html#QUERIES-LATERAL\"相对=nofollow> 横向 加盟这个代替。它是清洁，preferable，符合标准的方式把设定返回功能到 FROM 列表，而不是到 SELECT 清单：

You can put the set-returning function unnest() into the SELECT list like Raphaël suggests. But in Postgres 9.3 or later use a LATERAL join for this instead. It is the cleaner, preferable, standard-compliant way to put set-returning functions into the FROM list, not into the SELECT list:

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

一个细微差别：这滴空/空值的行从结果，因为 UNNEST（）收益没有行的，而同样被转换成在 NULL值FROM 列表反正返回。 100％的等效查询是：

One subtle difference: this drops rows with empty / NULL values from the result since unnest() returns no row, while the same is converted to a NULL value in the FROM list and returned anyway. The 100 % equivalent query is:

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

• What是横向和PostgreSQL中子查询的区别？


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

这篇关于PostgreSQL的：压扁的关系用一个阵列发射每个阵列入门一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！