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?
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.
@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)
with ordinality - is this answer also guaranteed to preserve order due to how it is implemented internally, or not guaranteed?
memberscolumn? Please show your table definition.