PostgreSQL 中两个数组的联合，无需取消嵌套
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24147969/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Union of two arrays in PostgreSQL without unnesting
提问by MrGlass
I have two arrays in PostgreSQL that I need to union. For example:
我在 PostgreSQL 中有两个需要联合的数组。例如：
{1,2,3}union {1,4,5}would return {1,2,3,4,5}
{1,2,3}工会{1,4,5}会回来{1,2,3,4,5}
Using the concatenate (||) operator would not remove duplicate entries, i.e. it returns {1,2,3,1,4,5}
使用连接 (||) 运算符不会删除重复的条目，即它返回 {1,2,3,1,4,5}
I found one solution on the web, however I do not like how it needs to unnest both arrays:
select ARRAY(select unnest(ARRAY[1,2,3]) as a UNION select unnest(ARRAY[2,3,4,5]) as a)
我在网上找到了一个解决方案，但是我不喜欢它如何取消嵌套两个数组：
select ARRAY(select unnest(ARRAY[1,2,3]) as a UNION select unnest(ARRAY[2,3,4,5]) as a)
Is there an operator or built-in function that will cleanly union two arrays?
是否有一个运算符或内置函数可以干净地联合两个数组？
回答by Clodoaldo Neto
If your problem is to unnest twice this will unnest only once
如果您的问题是取消嵌套两次，则只会取消嵌套一次
select array_agg(a order by a)
from (
select distinct unnest(array[1,2,3] || array[2,3,4,5]) as a
) s;
回答by Pavel Stehule
There is a extension intarray(in contrib package) that contains some useful functions and operators:
有一个扩展intarray（在 contrib 包中）包含一些有用的函数和运算符：
postgres=# create extension intarray ;
CREATE EXTENSION
with single pipe operator:
使用单管道操作符：
postgres=# select array[1,2,3] | array[3,4,5];
?column?
─────────────
{1,2,3,4,5}
(1 row)
or with uniqfunction:
或具有uniq功能：
postgres=# select uniq(ARRAY[1,2,3] || ARRAY[3,4,5]);
uniq
─────────────
{1,2,3,4,5}
(1 row)
ANSI/SQL knows a multiset, but it is not supported by PostgreSQL yet.
ANSI/SQL 知道多重集，但 PostgreSQL 尚不支持它。
回答by user48956
Can be done like so...
可以这样做...
select uniq(sort(array_remove(array_cat(ARRAY[1,2,3], ARRAY[1,4,5]), NULL)))
gives:
给出：
{1,2,3,4,5}
array_remove is needed because your can't sort arrays with NULLS.
Sort is needed because uniqde-duplicates only if adjacent elements are found.
需要 array_remove 是因为你不能用 NULLS 对数组进行排序。排序是必需的，因为uniq只有在找到相邻元素时才进行重复数据删除。
A benefit of this approach over @Clodoaldo Neto's is that works entire within the select, and doesn't the unnest in the FROM clause. This makes it straightforward to operate on multiple arrays columns at the same time, and in a single table-scan. (Although see Ryan Guill version as a function in the comment).
这种方法相对于@Clodoaldo Neto 的一个好处是，它可以在选择中完整工作，并且不会在 FROM 子句中取消嵌套。这使得在单个表扫描中同时对多个数组列进行操作变得简单。（尽管在评论中将 Ryan Guill 版本视为一个函数）。
Also, this pattern works for all array types (who's elements are sortable).
此外，这种模式适用于所有数组类型（谁的元素是可排序的）。
A downside is that, feasibly, its a little slower for longer arrays (due to the sort and the 3 intermediate array allocations).
一个缺点是，对于较长的数组，它可能会慢一点（由于排序和 3 个中间数组分配）。
I think both this and the accept answer fail if you want to keep NULL in the result.
如果您想在结果中保留 NULL，我认为这和接受答案都会失败。
