Subtracting an array from a Postgresql array without using a loop

user_9990267 0
Viewed 2330

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.enter image description here

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

postgresql arrays
3 Answers

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.

user_5931440

If you want to undo a concatenation, you can use slice syntax:

SELECT arr[ coalesce(array_length(prefix,1),0)+1 : ]

(online demo)

user_10485720

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

user_70865530
Related