Thread: update some elements in the array

Hello,

Could someone please tell me that is there anyway that I can update only
a few elements in a Postgres array.

eg; I have an array of text, declared as 'text[]'.

{"1","2","3","4","5"}  ==>  {"1","2","7","8","9"}

I want to update the last three element without affecting other
elements.  I could have done it by retrieving all elements in the array
first and the do a

UPDATE table
SET arr='{"1","2","3","4","5"}'
WHERE blahblah

however, that might be troublesome, so I tried

UPDATE table
SET arr[1]=val1
    arr[2]=val2...

but it didn't work.  If anyone knows, please tell me. Thanks for the
help.


- Bernie

Jouni Ahto wrote:

> On Thu, 13 Jul 2000, Bernie Huang wrote:
>
> > I want to update the last three element without affecting other
> > elements.  I could have done it by retrieving all elements in the array
> > first and the do a
> >
> > UPDATE table
> > SET arr='{"1","2","3","4","5"}'
> > WHERE blahblah
> >
> > however, that might be troublesome, so I tried
> >
> > UPDATE table
> > SET arr[1]=val1
> >     arr[2]=val2...
> >
> > but it didn't work.  If anyone knows, please tell me. Thanks for the
> > help.
>
> What does "it didn't work" in this case exactly mean?
>
> Did some testing with postgres, results;
>
> create table test (arr text[]);
> insert into test values ('{"1","2","3","4","5"}');
> select * from test;
>           arr
> -----------------------
>  {"1","2","3","4","5"}
> (1 row)
> update test set arr[4]='8', arr[2]='9';
> select * from test;
>           arr
> -----------------------
>  {"1","9","3","8","5"}
> (1 row)
>
> So, updating arrays definitely works. Are you having a syntax error
> somewhere or what?
>
> -- Jouni Ahto

Thanks. I forgot the quote...' '
(Man, that quote just drives me nuts...)


- Bernie

Bernie Huang <bernie.huang@ec.gc.ca> writes:
> however, that might be troublesome, so I tried
> UPDATE table
> SET arr[1]=val1
>     arr[2]=val2...
> but it didn't work.

Hmm, probably not ... I don't see any easy way around that, since
the array elements aren't really separate fields.  Under the hood
this isn't much different fromUPDATE table SET arr = something, arr = somethingElse
and the best we could really do for you is issue an error message
noting that only one of the assignments is going to win.

You can update several adjacent elements in the array with a slice
assignment, if that helps:

regression=# create table ff (a int[]);
CREATE
regression=# insert into ff values('{1,2,3,4,5,6}');
INSERT 399882 1
regression=# select * from ff;      a
---------------{1,2,3,4,5,6}
(1 row)

regression=# update ff set a[4:5] = '{44,55}';
UPDATE 1
regression=# select * from ff;       a
-----------------{1,2,3,44,55,6}
(1 row)

But if the elements aren't adjacent you'll have to do multiple UPDATEs.
        regards, tom lane