How to remove an item from integer array type

From: ChoonSoo Park <luispark(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: How to remove an item from integer array type
Date: 2013-02-20 16:24:17
Message-ID: CACgbiFuFnJ+6wO45JLv76jXyaBXCfbn3gx6F4GO7HLh0dV=_Nw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ian Lawrence Barwick 2013-02-20 16:47:19 Re: How to remove an item from integer array type
Previous Message Vincent Veyron 2013-02-20 15:13:35 Re: Perl function leading to out of memory error