Thread: Re: Problems with arrays

Harry Broomhall writes:
>    I am trying to use the array types in PgSQL, and am obviously missing
> something!
>
>
> swipe2test=> create table test_arr (id integer, ratecardlist integer[]);
> CREATE TABLE
> swipe2test=> insert into test_arr (id) values (1);
> INSERT 183981491 1
> swipe2test=> select * from test_arr;
>  id | ratecardlist
> ----+--------------
>   1 | (null)
> (1 row)
>
> swipe2test=> update test_arr set ratecardlist[1:3] = '{3,4,5}'  where id = 1;
> UPDATE 1
>
> swipe2test=> select * from test_arr;
>  id | ratecardlist
> ----+--------------
>   1 | (null)
> (1 row)
>
>    Er - huh?
>
>    So what did I miss?
>

  I forgot some important info with the above:

OS:       FreeBSD 5.0-RELEASE
PgSQL:    7.3.3

  Regards,
     Harry.

Harry Broomhall wrote:
>>swipe2test=> create table test_arr (id integer, ratecardlist integer[]);
>>CREATE TABLE
>>swipe2test=> insert into test_arr (id) values (1);
>>INSERT 183981491 1
>>swipe2test=> select * from test_arr;
>> id | ratecardlist
>>----+--------------
>>  1 | (null)
>>(1 row)
>>
>>swipe2test=> update test_arr set ratecardlist[1:3] = '{3,4,5}'  where id = 1;
>>UPDATE 1

While perhaps misleading, the answer is correct. You are appending array
elements to a NULL array, so the answer should be null. I think what you
really want here is:

regression=# update test_arr set ratecardlist = '{3,4,5}'  where id = 1;
UPDATE 1
regression=# select * from test_arr;
  id | ratecardlist
----+--------------
   1 | {3,4,5}
(1 row)

This actually replaces the NULL array instead of appending to it.

HTH,

Joe

Joe Conway writes:
> While perhaps misleading, the answer is correct. You are appending array
> elements to a NULL array, so the answer should be null. I think what you
> really want here is:
>
> regression=# update test_arr set ratecardlist = '{3,4,5}'  where id = 1;
> UPDATE 1
> regression=# select * from test_arr;
>   id | ratecardlist
> ----+--------------
>    1 | {3,4,5}
> (1 row)
>
> This actually replaces the NULL array instead of appending to it.
>
> HTH,

   It does indeed help!  Many thanks for that.

   <mischevous grin>
   Perhaps this ought to be documented a little more clearly?
   </mischevous grin>

   Regards,
      Harry.