How to remove an item from integer array type

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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:

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;

Regards,

Russell Keane
INPS

Tel: +44 (0)20 7501 7277
[cid:image001.jpg@01CE0F8B.75C5EF60]
Follow us<https://twitter.com/INPSnews&gt; on twitter | visit www.inps.co.uk<http://www.inps.co.uk/&gt;

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
[cid:image001.jpg@01CE0F8C.B0B01190]
Follow us<https://twitter.com/INPSnews&gt; on twitter | visit www.inps.co.uk<http://www.inps.co.uk/&gt;

It works!

Thank you,
Choon Park

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

You're absolutely correct Ian.
(It's getting late here too ;) )

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

Sorry,

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:

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

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