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)
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
