Обсуждение: Copying data from int column to array column

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?

Thanks for any help

Adam


--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

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)

On Wed, Jun 08, 2005 at 01:21:19PM +0100, Adam Witney wrote:
>
> UPDATE test SET field3[1] = field1;
>
> Why does the UPDATE of field2 work, but the UPDATE of field3 does not?

What version of PostgreSQL are you using?  The example should work
in 8.x.  See the Release Notes:

http://www.postgresql.org/docs/8.0/static/release-8-0.html

"Updating an element or slice of a NULL array value now produces a
non-NULL array result, namely an array containing just the assigned-to
positions."

In previous versions you can get around the problem by first setting
the column to an empty array:

UPDATE test SET field3 = '{}' WHERE field3 IS NULL;
UPDATE test SET field3[1] = field1;

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Adam Witney <awitney@sgul.ac.uk> writes:
> 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?

Works for me in 8.0 ;-).  Before 8.0, if you tried to assign to just one
value of an array that was initially NULL, the array stayed NULL.

(That behavior made sense from the point of view of the implementation,
which sees array element assignment as a binary operation taking an
array and a new element value ... but it wasn't especially useful.)

            regards, tom lane