Postgres Pro
Downloads
Home   >   mailing lists

Thread: Possible to UPDATE array[] columns?

Possible to UPDATE array[] columns?

From
"Blake Starkenburg"
Date:
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....

Thanks!

Re: Possible to UPDATE array[] columns?

From
Thom Brown
Date:
2009/10/30 Blake Starkenburg <blake@oldride.com>:
> 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....
>

You could use the concatenation operator

so it would be:

UPDATE TABLE set scores[] = scores[] || 12
WHERE id = 2;

Thom

Re: Possible to UPDATE array[] columns?

From
Pavel Stehule
Date:
2009/10/30 Blake Starkenburg <blake@oldride.com>:
> 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....
>
> Thanks!
>

postgres=# create table foo(a int[]);
CREATE TABLE
postgres=# insert into foo values('{}');
INSERT 0 1
postgres=# update foo set a = a || 10;
UPDATE 1
postgres=# update foo set a = a || 20;
UPDATE 1
postgres=# update foo set a = a || 30;
UPDATE 1
postgres=# select * from foo;
     a
------------
 {10,20,30}
(1 row)

regards
Pavel Stehule
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: Possible to UPDATE array[] columns?

From
Sam Mason
Date:
On Fri, Oct 30, 2009 at 10:47:26AM -0700, Blake Starkenburg wrote:
> 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}.

You need to use the normal array concatenation operator, ||, for
example:

  UPDATE table SET scores = scores || (
    SELECT scores FROM table WHERE id = 12)
  WHERE id = 2;

This pulls out the scores for id=12 and appends them onto all the scores
of the rows where id=2.  The reason for the sub-select is that any query
can only ever refer to the "current" row and not to any other row, the
way to get around this is to join tables together and then you are able
to compare every row with every other row.

--
  Sam  http://samason.me.uk/

Re: Possible to UPDATE array[] columns?

From
Sam Mason
Date:
On Fri, Oct 30, 2009 at 06:23:28PM +0000, Sam Mason wrote:
> On Fri, Oct 30, 2009 at 10:47:26AM -0700, Blake Starkenburg wrote:
> > 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}.
>
> You need to use the normal array concatenation operator, ||, for
> example:
>
>   UPDATE table SET scores = scores || (
>     SELECT scores FROM table WHERE id = 12)
>   WHERE id = 2;

Doh, sorry, I was thinking you were asking for something more
complicated.  I don't seem to be doing very well here at all today!

--
  Sam  http://samason.me.uk/

Re: Possible to UPDATE array[] columns?

From
Merlin Moncure
Date:
On Fri, Oct 30, 2009 at 1:47 PM, Blake Starkenburg <blake@oldride.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