Thread: 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

<br /><font face="sans-serif" size="2">Richard,</font><br /><br /><font face="sans-serif" size="2">So the solution can
be:</font><br /><font face="sans-serif" size="2">        (i) either write a function to insert the values into the
arrayone by one</font><br /><font face="sans-serif" size="2">        (ii) or else upgrade to 7.4 (or 8) to use the
ARRAYsyntax.</font><br /><br /><font face="sans-serif" size="2">Thanks a lot.</font><br /><br /><font face="sans-serif"
size="2">Regards<br/> Robert</font><br /><br /><br /><br /><table width="100%"><tr valign="top"><td width="40%"><font
face="sans-serif"size="1"><b>Richard Huxton <dev@archonet.com></b> </font><p><font face="sans-serif"
size="1">03/15/200509:08 AM</font></td><td width="59%"><table width="100%"><tr valign="top"><td><div
align="right"><fontface="sans-serif" size="1">To</font></div></td><td><font face="sans-serif"
size="1">Robert.Farrugia@go.com.mt</font></td></tr><trvalign="top"><td><div align="right"><font face="sans-serif"
size="1">cc</font></div></td><td><fontface="sans-serif" size="1">pgsql-sql@postgresql.org</font></td></tr><tr
valign="top"><td><divalign="right"><font face="sans-serif" size="1">Subject</font></div></td><td><font
face="sans-serif"size="1">Re: [SQL] Inserting values in arrays</font></td></tr></table><br /><table><tr
valign="top"><td></td><td></td></tr></table><br/></td></tr></table><br /><br /><br /><font
size="2"><tt>Robert.Farrugia@go.com.mtwrote:<br /> > CREATE TABLE test ( details varchar[]);<br /> > CREATE TABLE
test2( textvalue1 varchar, textvalue2 varchar);<br /> > INSERT INTO test2 VALUES ('Hello1', 'World1');<br /> >
INSERTINTO test2 VALUES ('hello2', 'World2');<br /><br /> > and I am expecting the following rows in test<br /> >
{'Hello1','World1'}<br /> > {'Hello2', 'World2'}<br /><br /> > Postgres version I am using is 7.3.4<br /><br />
Well,from 7.4 you can do:<br /><br /> INSERT INTO test SELECT ARRAY[textvalue1, textvalue2] FROM test2;<br /> INSERT 0
2<br/> richardh=> SELECT * FROM test;<br />      details<br /> -----------------<br />  {Hello1,World1}<br />
 {hello2,World2}<br/> (2 rows)<br /><br /> I think in 7.3 you might have to write your own function to assemble the <br
/>array. I'm not an array expert though, so might be worth checking the <br /> mailing list archives.<br /><br /> --<br
/>  Richard Huxton<br />   Archonet Ltd<br /></tt></font><br />