Re: How to remove an item from integer array type

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
>