Re: Copying data from int column to array column

On Jun 8, 2005, at 8:21 AM, Adam Witney wrote:

>
> Hi,
>
> I am trying to copy the data from an integer column into an array
> column in
> the same table. Something like this
>
> CREATE TABLE test (field1 INT, field2 INT, field3 INT[]);
>
> INSERT INTO test VALUES(1);
> INSERT INTO test VALUES(2);
> INSERT INTO test VALUES(3);
> INSERT INTO test VALUES(4);
> INSERT INTO test VALUES(5);
>
> UPDATE test SET field2 = field1;
> UPDATE test SET field3[1] = field1;
>
> Why does the UPDATE of field2 work, but the UPDATE of field3 does not?

Adam,

I'm not sure what you were expecting, but I tried things here and they
seemed to do what I expected:

Sean


CREATE TABLE test (field1 INT, field2 INT, field3 INT[]);

INSERT INTO test (field1) VALUES(1);
INSERT INTO test (field1) VALUES(2);
INSERT INTO test (field1) VALUES(3);
INSERT INTO test (field1) VALUES(4);
INSERT INTO test (field1) VALUES(5);
SELECT * FROM test;
UPDATE test SET field2 = field1;
SELECT * FROM test;
UPDATE test set field3[1] = field2;
SELECT * FROM test;
UPDATE test SET field3 = array((select field1 from test));
SELECT * FROM test;


-------------  OUTPUT ----------------

CREATE TABLE test (field1 INT, field2 INT, field3 INT[]);
CREATE TABLE
INSERT INTO test (field1) VALUES(1);
INSERT 147690348 1
INSERT INTO test (field1) VALUES(2);
INSERT 147690350 1
INSERT INTO test (field1) VALUES(3);
INSERT 147690352 1
INSERT INTO test (field1) VALUES(4);
INSERT 147690353 1
INSERT INTO test (field1) VALUES(5);
INSERT 147690355 1
SELECT * FROM test;
  field1 | field2 | field3
--------+--------+--------
       1 |        |
       2 |        |
       3 |        |
       4 |        |
       5 |        |
(5 rows)

UPDATE test SET field2 = field1;
UPDATE 5
SELECT * FROM test;
  field1 | field2 | field3
--------+--------+--------
       1 |      1 |
       2 |      2 |
       3 |      3 |
       4 |      4 |
       5 |      5 |
(5 rows)

UPDATE test set field3[1] = field2;
UPDATE 5
SELECT * FROM test;
  field1 | field2 | field3
--------+--------+--------
       1 |      1 | {1}
       2 |      2 | {2}
       3 |      3 | {3}
       4 |      4 | {4}
       5 |      5 | {5}
(5 rows)

UPDATE test SET field3 = array((select field1 from test));
UPDATE 5
SELECT * FROM test;
  field1 | field2 |   field3
--------+--------+-------------
       1 |      1 | {1,2,3,4,5}
       2 |      2 | {1,2,3,4,5}
       3 |      3 | {1,2,3,4,5}
       4 |      4 | {1,2,3,4,5}
       5 |      5 | {1,2,3,4,5}
(5 rows)