PostgreSQL中两个数组的联合，没有取消

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)

我在网上找到了一个解决方案，但是我不喜欢它需要如何取消两个阵列：选择ARRAY（选择不再（ARRAY [1,2,3]）作为UNION选择不需要（ARRAY [2,3,4， 5]）作为a）

Is there an operator or built-in function that will cleanly union two arrays?

是否有一个运算符或内置函数可以干净地结合两个数组？

3 个解决方案

#1

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;

#2

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 uniq function:

或者使用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还不支持它。

#3

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 uniq de-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相比，这种方法的好处是可以在select中使用整个，而在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，这个和接受答案都会失败。

#1