Re: Possible to UPDATE array[] columns?

On Fri, Oct 30, 2009 at 1:47 PM, Blake Starkenburg <blake(at)oldride(dot)com> wrote:
> Using SQL is it possible to UPDATE (append) onto an array[] column. For
> example say I have a column named "scores int[]".
>
> ID  | scores
> 2   | {54,14,21,8}
> 3   | {12,0,7}
>
> Now I want to append the score of 12 on row:ID 2 so the new scores would
> read {54,14,21,8,12}. I thought maybe simply leaving the array key empty
> would auto-append "UPDATE table set scores[] = 12 WHERE id = 2", not so....

As other mentioned, you want the || operator.

Just remember that all array operations read and write the entire
array. This is one of the reasons why arrays are pretty lousy for
OLTPish operations...they don't scale well and are inflexible in terms
of querying. Before putting the array in the table, always ask
yourself if you're better off with a separate table instead. The
advantage of arrays of course are they they are very compact and you
will get efficient use of the index on ID (if you are always
interested in all the scores for an ID, maybe array is a good choice).

merlin