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)