Union of two arrays in PostgreSQL without unnesting

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;

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.

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).

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).

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

There is a extension intarray (in 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 a multiset, but it is not supported by PostgreSQL yet.