Subtracting an array from a Postgresql array without using a loop

Question

What I basically want to do is mimic the array_cat functionality in a postgres table, but to subtract an array instead.

There is a function array_remove but it only removes one element at a time.

I can not figure out how to achieve this in a single query and i do not wish to use a loop. Any help would be appreciated.

array_removemany(ARRAY{1,2,3,4}, {1,2}) should give result {3,4}

Answer 1

It is without using loop but using query:

select array_agg(x)
from unnest('{1,3,2,4}'::numeric[]) as x
where x not in (1,2)
-- or x <> all('{1,2}'::numeric[])
;

┌───────────┐
│ array_agg │
├───────────┤
│ {3,4}     │
└───────────┘

You can to wrap this query into the function:

create function array_remove_many(anyarray, anyarray)
  returns anyarray
  language sql
  immutable
as $$
  select array_agg(x) from unnest($1) as x where x <> all($2)
$$;

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

┌───────────────────┐
│ array_remove_many │
├───────────────────┤
│ {3,4}             │
└───────────────────┘

You can to use it for any array types.

Answer 2

Although, I do agree that the accepted answer works, I use a different approach:

select
   array_agg(aa)
from
   unnest('{1,2,3,4}'::numeric[]) aa left join unnest('{1,2}'::numeric[]) ab on aa = ab
where
   ab is null

Hope that someone finds this little piece useful