Обсуждение: Inserting values in arrays
<br /><font face="sans-serif" size="2">I have the following issue.</font><br /><br /><font face="sans-serif"
size="2">Giventhe following tables:</font><br /><br /><font face="sans-serif" size="2">CREATE TABLE test ( details
varchar[]);</font><br/><font face="sans-serif" size="2">CREATE TABLE test2 ( textvalue1 varchar, textvalue2
varchar);</font><br/><font face="sans-serif" size="2">INSERT INTO test2 VALUES ('Hello1', 'World1');</font><br /><font
face="sans-serif"size="2">INSERT INTO test2 VALUES ('hello2', 'World2');</font><br /><br /><font face="sans-serif"
size="2">Iwould like to insert a row in test for each row of the test2 table i.e.</font><br /><font face="sans-serif"
size="2">INSERTINTO test (details) SELECT test2.textvalue1, test2.textvalue2 FROM test2</font><br /><br /><font
face="sans-serif"size="2">and I am expecting the following rows in test</font><br /><font face="sans-serif"
size="2">{'Hello1','World1'}</font><br /><font face="sans-serif" size="2">{'Hello2', 'World2'}</font><br /><br /><font
face="sans-serif"size="2">The above syntax is giving an error. How can this be done in postgres ?</font><br /><br
/><fontface="sans-serif" size="2">Postgres version I am using is 7.3.4</font><br /><br /><font face="sans-serif"
size="2">Regards<br/> Robert</font>
Robert.Farrugia@go.com.mt wrote:
> CREATE TABLE test ( details varchar[]);
> CREATE TABLE test2 ( textvalue1 varchar, textvalue2 varchar);
> INSERT INTO test2 VALUES ('Hello1', 'World1');
> INSERT INTO test2 VALUES ('hello2', 'World2');
> and I am expecting the following rows in test
> {'Hello1', 'World1'}
> {'Hello2', 'World2'}
> Postgres version I am using is 7.3.4
Well, from 7.4 you can do:
INSERT INTO test SELECT ARRAY[textvalue1, textvalue2] FROM test2;
INSERT 0 2
richardh=> SELECT * FROM test; details
----------------- {Hello1,World1} {hello2,World2}
(2 rows)
I think in 7.3 you might have to write your own function to assemble the
array. I'm not an array expert though, so might be worth checking the
mailing list archives.
-- Richard Huxton Archonet Ltd
Richard,
So the solution can be:
(i) either write a function to insert the values into the array one by one
(ii) or else upgrade to 7.4 (or 8) to use the ARRAY syntax.
Thanks a lot.
Regards
Robert
| Richard Huxton <dev@archonet.com> 03/15/2005 09:08 AM |
|
Robert.Farrugia@go.com.mt wrote:
> CREATE TABLE test ( details varchar[]);
> CREATE TABLE test2 ( textvalue1 varchar, textvalue2 varchar);
> INSERT INTO test2 VALUES ('Hello1', 'World1');
> INSERT INTO test2 VALUES ('hello2', 'World2');
> and I am expecting the following rows in test
> {'Hello1', 'World1'}
> {'Hello2', 'World2'}
> Postgres version I am using is 7.3.4
Well, from 7.4 you can do:
INSERT INTO test SELECT ARRAY[textvalue1, textvalue2] FROM test2;
INSERT 0 2
richardh=> SELECT * FROM test;
details
-----------------
{Hello1,World1}
{hello2,World2}
(2 rows)
I think in 7.3 you might have to write your own function to assemble the
array. I'm not an array expert though, so might be worth checking the
mailing list archives.
--
Richard Huxton
Archonet Ltd