CODEWITHSUNDEEP
sqlarrayspostgresql
dumbledown
dumbledown

Reputation: 43

Postgresql - Array_remove for elements from select

I have a column (members) with an array that has a varying number of elements.

e.g.

id   members (integer[])
1    {1, 3, 5, 7}
2    {4, 5, 7, 9}

I have a table (unwanted_members) with members that I want removed if they exist, e.g.

member
1
7

I want to remove these members from the arrays, i.e.

id  members
1   {3, 5}
2   {4, 5, 9}

Can anyone suggest a simple methodology?

Upvotes: 4

Views: 6254

Answers (2)

Pavel Stehule
Pavel Stehule

Reputation: 45770

@a_horse_with_no_name solution is perfect if extension intarray is available. If not, then you can use a table operators for some operations UNION, INTERSECT, EXCEPT.

CREATE OR REPLACE FUNCTION array_remove(anyarray, anyarray)
RETURNS anyarray AS $$
SELECT ARRAY(SELECT unnest($1) EXCEPT SELECT unnest($2))
$$ LANGUAGE sql;

postgres=> select array_remove(ARRAY[1, 3, 5, 7],ARRAY[1,7]);
┌──────────────┐
│ array_remove │
╞══════════════╡
│ {3,5}        │
└──────────────┘
(1 row)

Upvotes: 2

user330315
user330315

Reputation:

If you install the intarray extension this is quite easy: 

select id, members - (select array_agg(member) from unwanted_members)
from foo;

To update the table you can use:

update foo
    set members = members - (select array_agg(member) from unwanted_members);

Without that extension, you first need to unnest the member IDs remove the unwanted and then aggregate them back to an array:

select f.id, array_agg(t.mid order by t.idx) as members
from foo f, unnest(f.members) with ordinality as t(mid,idx)
where t.mid not in (select member from unwanted_members)
group by f.id;

To update the existing values, use this:

update foo
  set members = x.members
from (
  select f.id, array_agg(t.mid order by t.idx) as members
  from foo f, unnest(f.members) with ordinality as t(mid,idx)
  where t.mid not in (select id from unwanted_members)
  group by f.id
) x
where foo.id = x.id;

Assuming id is the primary key of the table.

Upvotes: 3

Related Questions