Ensuring unique elements in array created from merging 2 bigint arrays

Question

What is the most efficient way to ensure the uniqueness of values in a bigint array created from merging of 2 other bigint arrays?

For example, this operation select ARRAY[1,2] || ARRAY[2, 3] should give as a result 1,2,3. I have checked the extension intarray and see it does not work with bigint.

Answer 1

You need to write your own function for that.

create function concat_unique(p_array_one bigint[], p_array_two bigint[])
  returns bigint[]
as
$$
select array_agg(x order by x)
from (
  select x
  from unnest(p_array_one) as t(x)
  union
  select x
  from unnest(p_array_two) as t(x)
) t
$$
language sql
immutable;

And then:

select concat_unique(array[1,2], array[2,3,4]);

returns

concat_unique
-------------
{1,2,3,4}