Postgresql - Array_remove for elements from select

Question

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?

What is the data type of your `members` column? Please show your table definition. — Shuwn Yuan Tee, Apr 03 '18 at 09:09

score 3 · Accepted Answer · answered Apr 03 '18 at 09:21

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.

score 2 · Answer 2 · answered Apr 03 '18 at 11:01

@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)

@a_horse_with_no_name's answer is guaranteed to preserve order since it uses `with ordinality` - is this answer also guaranteed to preserve order due to how it is implemented internally, or not guaranteed? — Pyrocks, Jan 21 '20 at 07:51

Postgresql - Array_remove for elements from select

2 Answers2