Union of two arrays in PostgreSQL without unnesting

Question

I have two arrays in PostgreSQL that I need to union. For example:

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

Using the concatenate (||) operator would not remove duplicate entries, i.e. it returns {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)

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

Answer 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;