pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedInserting values in arrays
3+ messages / 2 participants
[nested] [flat]
* Inserting values in arrays
@ 2005-03-14 17:53 Robert.Farrugia@go.com.mt
0 siblings, 1 reply; 3+ messages in thread
From: Robert.Farrugia@go.com.mt @ 2005-03-14 17:53 UTC (permalink / raw)
To: pgsql-sql
I have the following issue.
Given the following tables:
CREATE TABLE test ( details varchar[]);
CREATE TABLE test2 ( textvalue1 varchar, textvalue2 varchar);
INSERT INTO test2 VALUES ('Hello1', 'World1');
INSERT INTO test2 VALUES ('hello2', 'World2');
I would like to insert a row in test for each row of the test2 table i.e.
INSERT INTO test (details) SELECT test2.textvalue1, test2.textvalue2 FROM
test2
and I am expecting the following rows in test
{'Hello1', 'World1'}
{'Hello2', 'World2'}
The above syntax is giving an error. How can this be done in postgres ?
Postgres version I am using is 7.3.4
Regards
Robert
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Inserting values in arrays
@ 2005-03-15 08:08 Richard Huxton <dev@archonet.com>
parent: Robert.Farrugia@go.com.mt
0 siblings, 1 reply; 3+ messages in thread
From: Richard Huxton @ 2005-03-15 08:08 UTC (permalink / raw)
To: Robert.Farrugia@go.com.mt; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Inserting values in arrays
@ 2005-03-15 09:00 Robert.Farrugia@go.com.mt
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 3+ messages in thread
From: Robert.Farrugia@go.com.mt @ 2005-03-15 09:00 UTC (permalink / raw)
To: dev@archonet.com; +Cc: pgsql-sql
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
To
Robert.Farrugia@go.com.mt
cc
pgsql-sql@postgresql.org
Subject
Re: [SQL] Inserting values in arrays
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-03-15 09:00 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-14 17:53 Inserting values in arrays Robert.Farrugia@go.com.mt
2005-03-15 08:08 ` Richard Huxton <dev@archonet.com>
2005-03-15 09:00 ` Robert.Farrugia@go.com.mt
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox