CODEWITHSUNDEEP
arrayspostgresql
Rockboy987
Rockboy987

Reputation: 181

Subtracting an array from a Postgresql array without using a loop

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}

Upvotes: 11

Views: 6191

Answers (4)

iyxan23
iyxan23

Reputation: 693

This is a rather old question, but I would also like to share this simpler method that uses the EXCEPT operator; worked pretty well for me:

select array(
    select unnest('{1,2,3,4}'::numeric[])
    except
    select unnest('{1,2}'::numeric[])
);

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

Upvotes: 1

Euller Pereira
Euller Pereira

Reputation: 11

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

Upvotes: 1

Abelisto
Abelisto

Reputation: 15624

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.

Upvotes: 11

Bergi
Bergi

Reputation: 664971

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

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

(online demo)

Upvotes: 0

Related Questions