带有空数组的PostgreSQL嵌套 [英] PostgreSQL unnest with empty array
问题描述
我使用postgreSQL 9.1。在我的数据库中,有一个表看起来像
I use postgreSQL 9.1. In my database there is a table which looks like
id | ... | values
-----------------------
1 | ... | {1,2,3}
2 | ... | {}
其中id是整数,而值是整数数组。数组可以为空。
where id is an integer and values is an integer array. The arrays can be empty.
我需要取消嵌套此列表。如果我查询
I need to unnest this list. If I query
select id, ..., unnest(values)
from table
我得到id为1的三行(如预期),并且id = 2的行没有。有没有办法得到一个结果类似
I get three rows for id = 1 (as expected) and no lines for id = 2. Is there a way to get a result like
id | ... | unnest
-------------------
1 | ... | 1
1 | ... | 2
1 | ... | 3
2 | ... | null
即
推荐答案
select id,
case
when int_values is null or array_length(int_values,1) is null then null
else unnest(int_values)
end as value
from the_table;
(请注意,我将列重命名为 values
到 int_values
,因为 values
是保留字,不应用作列名。
(note that I renamed the column values
to int_values
as values
is a reserved word and should not be used as a column name).
SQLFiddle: http://sqlfiddle.com/#!1 / a0bb4 / 1
SQLFiddle: http://sqlfiddle.com/#!1/a0bb4/1
Postgres 10不允许使用 unnest()
这样。
Postgres 10 does not allow to use unnest()
like that any more.
您需要使用横向联接:
select id, t.i
from the_table
cross join lateral unnest(coalesce(nullif(int_values,'{}'),array[null::int])) as t(i);
在线示例: http://rextester.com/ALNX23313
使用时可以进一步简化左联接而不是交叉联接:
It can be simplified even further when using a left join instead of the cross join:
select id, t.i
from the_table
left join lateral unnest(int_values) as t(i) on true;
在线示例: http://rextester.com/VBO52351
这篇关于带有空数组的PostgreSQL嵌套的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!