Updating Arrays

On Mon, Aug 22, 2011 at 12:54 PM, Bob Pawley <rjpawley@shaw.ca> wrote:

Hi

I exploring the use of arrays.

So far I have created a table and inserted a row and updated the row with an
array
Update library.compare
  set _base =
  '{2, 0.764149497122068, 4.8886}'
  where process_id = 2;
– successfully.

However when I attempt to update a specific element of the array

Update library.compare
set _base[2] =
'{2}'
where process_id = 2;
I get an error “invalid input syntax for type numeric: "{2}"”

try "set base[2] = 2"

merlin

I'm not sure what you mean.

_base -- not base -- is the column name.

Bob

-----Original Message-----
From: Merlin Moncure
Sent: Monday, August 22, 2011 11:03 AM
To: Bob Pawley
Cc: Postgresql
Subject: Re: [GENERAL] Updating Arrays

On Mon, Aug 22, 2011 at 12:54 PM, Bob Pawley <rjpawley@shaw.ca> wrote:

Hi

I exploring the use of arrays.

So far I have created a table and inserted a row and updated the row with
an
array
Update library.compare
set _base =
'{2, 0.764149497122068, 4.8886}'
where process_id = 2;
� successfully.

However when I attempt to update a specific element of the array

Update library.compare
set _base[2] =
'{2}'
where process_id = 2;
I get an error �invalid input syntax for type numeric: "{2}"�

try "set base[2] = 2"

merlin

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

-----Original Message----- From: Merlin Moncure
Sent: Monday, August 22, 2011 11:03 AM
To: Bob Pawley
Cc: Postgresql
Subject: Re: [GENERAL] Updating Arrays

On Mon, Aug 22, 2011 at 12:54 PM, Bob Pawley <rjpawley@shaw.ca> wrote:

Hi

I exploring the use of arrays.

So far I have created a table and inserted a row and updated the row with
an
array
Update library.compare
 set _base =
 '{2, 0.764149497122068, 4.8886}'
 where process_id = 2;
– successfully.

However when I attempt to update a specific element of the array

Update library.compare
set _base[2] =
'{2}'
where process_id = 2;
I get an error “invalid input syntax for type numeric: "{2}"”

try "set base[2]  = 2"

On Mon, Aug 22, 2011 at 8:49 PM, Bob Pawley <rjpawley@shaw.ca> wrote:

I'm not sure what you mean.

_base -- not base -- is the column name.

oops. anyways, the important point is on the right side of the equals sign.

Update library.compare set _base[2] = 2 where process_id = 2;

(also, please try to avoid top posting)

merlin

-----Original Message----- From: Merlin Moncure
Sent: Monday, August 22, 2011 11:03 AM
To: Bob Pawley
Cc: Postgresql
Subject: Re: [GENERAL] Updating Arrays

On Mon, Aug 22, 2011 at 12:54 PM, Bob Pawley <rjpawley@shaw.ca> wrote:

Hi

I exploring the use of arrays.

So far I have created a table and inserted a row and updated the row with an
array
Update library.compare
set _base =
'{2, 0.764149497122068, 4.8886}'
where process_id = 2;
– successfully.

However when I attempt to update a specific element of the array

Update library.compare
set _base[2] =
'{2}'
where process_id = 2;
I get an error “invalid input syntax for type numeric: "{2}"”

try "set base[2] = 2"

merlin

On Aug 22, 2011, at 21:49, "Bob Pawley" <rjpawley@shaw.ca> wrote:

I'm not sure what you mean.

_base -- not base -- is the column name.

Bob

Your column name has a leading underscore? On the table or is it an alias? Regardless is it that hard to replace the mis-typed column name with the correct one?

Compare these statements:
Original
... Set base = {1,2,3};
SELECT base[2] ... ;

Updates
Set base[2] = {2}; -- replaces the second value with an array of 1 dimension and a single value.
set base[2] = 2; -- replaces the second value with a single numeric value

Now, from the above select, is the second value an array or a single value?

And please do not top-post. Personally it isn't that big a deal to me but since the first reply was bottom posted you should follow suit. Even when you reply first on this list it is customary to bottom-post. When in Rome...

David J.

Merlin Moncure <mmoncure@gmail.com> writes:

On Mon, Aug 22, 2011 at 8:49 PM, Bob Pawley <rjpawley@shaw.ca> wrote:

I'm not sure what you mean.
_base -- not base -- is the column name.

oops. anyways, the important point is on the right side of the equals sign.
Update library.compare set _base[2] = 2 where process_id = 2;

Right. If you assign to _base[2], you must provide a single numeric
value. Not a one-element array.

OTOH, if you were to assign to _base[2:2] (an array slice containing
just that one element), you must provide a one-element numeric array on
the righthand side. So if there's some reason why you need to write
'{2}' and not just '2', you can accommodate that by adjusting what
you're assigning to.

regards, tom lane