Re: How to remove an item from integer array type

2013/2/21 Russell Keane <Russell(dot)Keane(at)inps(dot)co(dot)uk>
>
>
>
> >>Sorry,
>
> >>
>
> >>It's not ordered by value. It's not sorted list unfortunately. It can be '{100, 120, 102, 130, 104}'.
>
> >>
>
> >>Do you have other suggestion?
>
> >>
>
> >>Thank you,
>
> >>Choon Park
>
> >
>
> >This should work:
>
> >update a set f2 = (select array_agg(X.id) from (select f1, unnest(f2) id from a where f1 = 1 except select 1 f1, 101 id) X) where f1 = 1;
>
>
>
> And with the correct table name:
>
> update tableA set f2 = (select array_agg(X.id) from (select f1, unnest(f2) id from tableA where f1 = 1 except select 1 f1, 101 id) X) where f1 = 1;

I don't think that will work, except accidentally;

testdb=# CREATE TABLE tablea(f1 int, f2 int[]);
CREATE TABLE
testdb=# INSERT INTO tablea values(1, '{100,101,102,103,99,104,22}');
INSERT 0 1
testdb=# BEGIN ;
BEGIN
testdb=*# update tableA set f2 = (select array_agg(X.id) from (select
f1, unnest(f2) id from tablea where f1 = 1 except select 1 f1, 101 id)
x) where f1=1;
UPDATE 1
testdb=*# SELECT * from tablea ;
f1 | f2
----+-------------------------
1 | {100,22,103,99,104,102}
(1 row)

testdb=*# ROLLBACK ;
ROLLBACK

Moving the exclusion operation up a level seems to do the trick:

testdb=# BEGIN ;
BEGIN
testdb=*# update tableA set f2 = (select array_agg(X.id) from (select
f1, unnest(f2) id from tablea where f1 = 1) x WHERE x.id != 101) where
f1=1;
UPDATE 1
testdb=*# SELECT * from tablea ;
f1 | f2
----+-------------------------
1 | {100,102,103,99,104,22}
(1 row)

(It's a bit late where I am so I might be overlooking something)

Ian Barwick