Combining two arrays in PostgreSQL without unnecessary

Question

I have two arrays in PostgreSQL that I need to join. For instance:

{1,2,3} union {1,4,5} will return {1,2,3,4,5}

Using the concatenate (||) operator does not delete duplicate entries, i.e. returns {1,2,3,1,4,5}

I found one solution on the Internet, but I don’t like how to disconnect both arrays: 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 purely combine the two arrays?

Answer 1

If your problem is to protest twice, this will disable 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; 

Answer 2

There is an intarray extension (in the contrib package) that contains some useful functions and operators:

 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:

 postgres=# select uniq(ARRAY[1,2,3] || ARRAY[3,4,5]); uniq ───────────── {1,2,3,4,5} (1 row) 

ANSI / SQL knows the multiset, but it is not yet supported by PostgreSQL.

Answer 3

You can do 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 necessary because you cannot sort arrays using NULLS. Sorting is necessary because uniq deduplicated only if neighboring elements are found.

The advantage of this approach with respect to @Clodoaldo Neto is that it works entirely within the selection and is no exception in the FROM clause. This simplifies working with multiple columns of arrays simultaneously and in a single table scan. (Although see the Ryan Guill Version as a function in the comment).

In addition, this template works for all types of arrays (which elements are sorted).

The disadvantage is that, essentially, it is slightly slower for longer arrays (due to the sorting and distribution of 3 intermediate arrays).

I think that both this and the accept answer fail if you want to keep NULL as a result.