Re: update some elements in the array

Bernie Huang <bernie(dot)huang(at)ec(dot)gc(dot)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 from
UPDATE 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