Skip site navigation (1) Skip section navigation (2)

Re: Possible to UPDATE array[] columns?

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Blake Starkenburg <blake(at)oldride(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Possible to UPDATE array[] columns?
Date: 2009-10-31 13:21:59
Message-ID: b42b73150910310621g294ba203jdf797c341d188c4e@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

pgsql-general by date

Next:From: Merlin MoncureDate: 2009-10-31 13:32:14
Subject: sudoku in an sql statement
Previous:From: Alban HertroysDate: 2009-10-31 11:52:01
Subject: Re: Absolute value of intervals

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group