Thread: array [] vs {}

Hi,
I am attempting to use arrays in a db for the first time and am having
strife:
[] notation work only on non-empty initialized arrays. Is this
a feature/bug I should know about? For example:

create table test (
    myname  varchar(50),
    myarray int[]
);


INSERT INTO test (myname) values ('bobo');

UPDATE test SET myarray[1] = 5;

SELECT * FROM TEST;
 myname | myarray
--------+---------
 bobo   |
(1 row)

-- An empty array column is not what I'd expect
-- but curly brackets work

UPDATE test SET myarray = '{5}';

SELECT * FROM TEST;
 myname | myarray
--------+---------
 bobo   | {5}
(1 row)


-- However If I revert to [] notation I can do as I please:

UPDATE test SET myarray[2] = 50;
SELECT * FROM TEST;
 myname | myarray
--------+---------
 bobo   | {5,50}
(1 row)

- [] works as expected?

Any tips for getting myarray[1] to behave?

Sean

Sean Mullen wrote:
> Any tips for getting myarray[1] to behave?
>

You are trying to *append* an element to a NULL array, not an empty
array. It is analogous to doing this:

regression=# select NULL || 'hello';
  ?column?
----------

(1 row)

However, at least for 7.3.x and earlier, there is no way to append an
element to an empty array either. In either case, by setting the column
to a non-NULL and non-empty array, you can then append elements
successfully.

Appending to an empty array is fixed as of a patch recently submitted
and will hopefully be included in 7.4 when it is released:

regression=# create table test (myname text, myarray int[]);
CREATE TABLE
regression=# INSERT INTO test (myname) values ('bobo');
INSERT 1218925 1
regression=# UPDATE test SET myarray[1] = 5;
UPDATE 1
regression=# SELECT * FROM TEST;
  myname | myarray
--------+---------
  bobo   |
(1 row)

regression=# UPDATE test SET myarray = '{}';
UPDATE 1
regression=# UPDATE test SET myarray[2] = 50;
UPDATE 1
regression=# SELECT * FROM TEST;
  myname | myarray
--------+---------
  bobo   | {50}
(1 row)

HTH,

Joe