Обсуждение: How to remove an item from integer array type

Hello Gurus,

Table A has integer[] column. I need to delete specific integer value from
that column.

f1     |     f2
1           {100, 101, 102, 103}
2           {200, 300, 400}

I want to remove 101 from f2 and also preserve the order.

f1     |     f2
1           {100, 102, 103}
2           {200, 300, 400}

I tried the following query and it did remove the 101 but it didn't
preserve the order.
update tableA set f2 = (select array_agg(X.id) from (select unnest(f2) id
except select 101 id) X) where f1 = 1;

What's the best way to do this?

Thank you,
Choon Park

2013/2/21 ChoonSoo Park <luispark@gmail.com>
>
> Hello Gurus,
>
> Table A has integer[] column. I need to delete specific integer value from that column.
>
> f1     |     f2
> 1           {100, 101, 102, 103}
> 2           {200, 300, 400}
>
> I want to remove 101 from f2 and also preserve the order.
>
> f1     |     f2
> 1           {100, 102, 103}
> 2           {200, 300, 400}
>
> I tried the following query and it did remove the 101 but it didn't preserve the order.
> update tableA set f2 = (select array_agg(X.id) from (select unnest(f2) id except select 101 id) X) where f1 = 1;
>
> What's the best way to do this?

Assuming you want to keep the values in numeric order, add an ORDER BY:

update tableA set f2 = (select array_agg(X.id) from (select unnest(f2)
id except select 101 id ORDER BY id) X) where f1 = 1;

HTH

Ian Barwick

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

On Wed, Feb 20, 2013 at 11:47 AM, Ian Lawrence Barwick <barwick@gmail.com>wrote:

> 2013/2/21 ChoonSoo Park <luispark@gmail.com>
> >
> > Hello Gurus,
> >
> > Table A has integer[] column. I need to delete specific integer value
> from that column.
> >
> > f1     |     f2
> > 1           {100, 101, 102, 103}
> > 2           {200, 300, 400}
> >
> > I want to remove 101 from f2 and also preserve the order.
> >
> > f1     |     f2
> > 1           {100, 102, 103}
> > 2           {200, 300, 400}
> >
> > I tried the following query and it did remove the 101 but it didn't
> preserve the order.
> > update tableA set f2 = (select array_agg(X.id) from (select unnest(f2)
> id except select 101 id) X) where f1 = 1;
> >
> > What's the best way to do this?
>
> Assuming you want to keep the values in numeric order, add an ORDER BY:
>
> update tableA set f2 = (select array_agg(X.id) from (select unnest(f2)
> id except select 101 id ORDER BY id) X) where f1 = 1;
>
> HTH
>
> Ian Barwick
>

It works!

Thank you,
Choon Park

On Wed, Feb 20, 2013 at 12:06 PM, Russell Keane <Russell.Keane@inps.co.uk>wrote:

> ** **
>
> >>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;**
> **
>
> ** **
>
> ** **
>
> ** **
>
> Regards,****
>
> ** **
>
> *Russell Keane***
>
> *INPS*****
>
> * *
>
> Tel:         +44 (0)20 7501 7277****
>
> [image: cid:image001.jpg@01CDBE9B.11D013F0]****
>
> Follow us <https://twitter.com/INPSnews> on twitter | visit www.inps.co.uk
> ****
>
> ** **
>
> ** **
>
> ** **
>

2013/2/21 Russell Keane <Russell.Keane@inps.co.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
1f1, 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

Pk1vdmluZyB0aGUgZXhjbHVzaW9uIG9wZXJhdGlvbiB1cCBhIGxldmVsIHNlZW1zIHRvIGRvIHRo
ZSB0cmljazoNCj4NCj50ZXN0ZGI9IyBCRUdJTiA7DQo+QkVHSU4NCj50ZXN0ZGI9KiMgdXBkYXRl
IHRhYmxlQSBzZXQgZjIgPSAoc2VsZWN0IGFycmF5X2FnZyhYLmlkKSBmcm9tIChzZWxlY3QgZjEs
IHVubmVzdChmMikgaWQgZnJvbSB0YWJsZWEgd2hlcmUgZjEgPSAxKSB4IFdIRVJFIHguaWQgIT0g
MTAxKSB3aGVyZSBmMT0xOyBVUERBVEUgMSB0ZXN0ZGI9KiMgU0VMRUNUICogZnJvbSB0YWJsZWEg
Ow0KPiBmMSB8ICAgICAgICAgICBmMg0KPi0tLS0rLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQ0K
PiAgMSB8IHsxMDAsMTAyLDEwMyw5OSwxMDQsMjJ9DQo+ICgxIHJvdykNCj4NCj4gKEl0J3MgYSBi
aXQgbGF0ZSB3aGVyZSAgSSBhbSBzbyBJIG1pZ2h0IGJlIG92ZXJsb29raW5nIHNvbWV0aGluZykN
Cj4NCj4NCj5JYW4gQmFyd2ljaw0KDQpZb3UncmUgYWJzb2x1dGVseSBjb3JyZWN0IElhbi4NCihJ
dCdzIGdldHRpbmcgbGF0ZSBoZXJlIHRvbyA7KSApDQo=

On Feb 20, 2013, at 17:51, ChoonSoo Park <luispark@gmail.com> wrote:

> Sorry,
>=20
> It's not ordered by value. It's not sorted list unfortunately. It can =
be '{100, 120, 102, 130, 104}'.

Are you saying it's an unordered list for which the order matters? That =
seems a bit peculiar.

What would probably work is to split the array around the value to =
remove, and merge those arrays again.
Something like this:

=3D> select (ARRAY[100, 101, 102, 103, 104])[1:2] || (ARRAY[100, 101, =
102, 103, 104])[4:5];
 ?column?
 -------------------
 {100,101,103,104}
 (1 row)=20
    =20

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.