Search Postgresql Archives

Re: Copying data from int column to array column

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux