pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedINSERT INTO ... SELECT
6+ messages / 5 participants
[nested] [flat]
* INSERT INTO ... SELECT
@ 2003-08-11 10:21 Silke Trissl <trissl@informatik.hu-berlin.de>
2003-08-12 01:57 ` Re: INSERT INTO ... SELECT Christopher Browne <cbbrowne@acm.org>
2003-08-12 16:40 ` Re: INSERT INTO ... SELECT Jonathan Gardner <jgardner@jonathangardner.net>
0 siblings, 2 replies; 6+ messages in thread
From: Silke Trissl @ 2003-08-11 10:21 UTC (permalink / raw)
To: pgsql-sql
Hi,
I would like to insert into a table values from a table and user defined
ones. Here is the example:
I found this statement to insert values from another table:
INSERT INTO test_table (cust_id, cust_name) SELECT id, name from CUSTOMER;
But the test_table has another column, which should have the same value
for all the customers.
Is there something like
INSERT INTO test_table (int_id, cust_id, cust_name) '1', SELECT id, name
from CUSTOMER:
and if so, what ist the correct statement? If not, what is an
alternative to insert a single row at a time?
Thanks in advance
Silke
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: INSERT INTO ... SELECT
2003-08-11 10:21 INSERT INTO ... SELECT Silke Trissl <trissl@informatik.hu-berlin.de>
@ 2003-08-12 01:57 ` Christopher Browne <cbbrowne@acm.org>
1 sibling, 0 replies; 6+ messages in thread
From: Christopher Browne @ 2003-08-12 01:57 UTC (permalink / raw)
To: pgsql-sql
Centuries ago, Nostradamus foresaw when trissl@informatik.hu-berlin.de (Silke Trissl) would write:
> I would like to insert into a table values from a table and user
> defined ones. Here is the example:
>
> I found this statement to insert values from another table:
>
> INSERT INTO test_table (cust_id, cust_name) SELECT id, name from CUSTOMER;
>
> But the test_table has another column, which should have the same
> value for all the customers.
>
> Is there something like
>
> INSERT INTO test_table (int_id, cust_id, cust_name) '1', SELECT id,
> name from CUSTOMER:
>
> and if so, what ist the correct statement? If not, what is an
> alternative to insert a single row at a time?
You're close.
The constant term needs to be inside the SELECT.
Try:
insert into test_table (int_id, cust_id, cust_name)
select '1', id, name from customer;
--
select 'cbbrowne' || '@' || 'acm.org';
http://www.ntlug.org/~cbbrowne/sap.html
(eq? 'truth 'beauty) ; to avoid unassigned-var error, since compiled code
; will pick up previous value to var set!-ed,
; the unassigned object.
-- from BBN-CL's cl-parser.scm
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: INSERT INTO ... SELECT
2003-08-11 10:21 INSERT INTO ... SELECT Silke Trissl <trissl@informatik.hu-berlin.de>
@ 2003-08-12 16:40 ` Jonathan Gardner <jgardner@jonathangardner.net>
1 sibling, 0 replies; 6+ messages in thread
From: Jonathan Gardner @ 2003-08-12 16:40 UTC (permalink / raw)
To: Silke Trissl <trissl@informatik.hu-berlin.de>; pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Monday 11 August 2003 03:21, Silke Trissl wrote:
>
> Is there something like
>
> INSERT INTO test_table (int_id, cust_id, cust_name) '1', SELECT id,
> name from CUSTOMER:
>
> and if so, what ist the correct statement? If not, what is an
> alternative to insert a single row at a time?
>
I'm not too sure on what you want, but I'll guess.
INSERT INTO test_table
(int_id, cust_id, cust_name)
SELECT
1, id, name
FROM customer;
Does this do what you want?
- --
Jonathan Gardner <jgardner@jonathangardner.net>
Live Free, Use Linux!
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
iD8DBQE/ORhhWgwF3QvpWNwRAsoTAJ9Wq+iZ3/JbapXeS1wrv9VgR1gtAgCfVItU
ZsBeXCPBuLuOgBTWn3vu2Gc=
=PJDU
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: INSERT INTO ... SELECT
@ 2003-08-12 23:06 Vernon Smith <vwu98034@lycos.com>
0 siblings, 0 replies; 6+ messages in thread
From: Vernon Smith @ 2003-08-12 23:06 UTC (permalink / raw)
To: pgsql-sql@postgresql.org, "Christopher Browne" <cbbrowne@acm.org>
Coincidentally, I have a very similar case using some twists.
The table I want to insert data is something like
table A (
user01 int,
user02 int,
...
primary key (user01, user02),
CHECK ( user01 < user02 )
);
And the user table is:
tabe user_table (
user int constraint pk_user primary key UNIQUE, --PK
...,
email varchar(25) not null,
...
};
In the insertion statement, the user02 is obtained by a subselect statement: "select user from user where email=?".
There may not exist the email in the user table. And if yes, the check condition may not be met. If the condition is not met, the two user IDs need to be switched.
How the query shall be construted?
Thanks for any suggestions.
v.
--
--------- Original Message ---------
DATE: Mon, 11 Aug 2003 21:57:05
From: Christopher Browne <cbbrowne@acm.org>
To: pgsql-sql@postgresql.org
Cc:
>Centuries ago, Nostradamus foresaw when trissl@informatik.hu-berlin.de (Silke Trissl) would write:
>> I would like to insert into a table values from a table and user
>> defined ones. Here is the example:
>>
>> I found this statement to insert values from another table:
>>
>> INSERT INTO test_table (cust_id, cust_name) SELECT id, name from CUSTOMER;
>>
>> But the test_table has another column, which should have the same
>> value for all the customers.
>>
>> Is there something like
>>
>> INSERT INTO test_table (int_id, cust_id, cust_name) '1', SELECT id,
>> name from CUSTOMER:
>>
>> and if so, what ist the correct statement? If not, what is an
>> alternative to insert a single row at a time?
>
>You're close.
>
>The constant term needs to be inside the SELECT.
>
>Try:
> insert into test_table (int_id, cust_id, cust_name)
> select '1', id, name from customer;
>--
>select 'cbbrowne' || '@' || 'acm.org';
>http://www.ntlug.org/~cbbrowne/sap.html
>(eq? 'truth 'beauty) ; to avoid unassigned-var error, since compiled code
> ; will pick up previous value to var set!-ed,
> ; the unassigned object.
>-- from BBN-CL's cl-parser.scm
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
____________________________________________________________
Get advanced SPAM filtering on Webmail or POP Mail ... Get Lycos Mail!
http://login.mail.lycos.com/r/referral?aid=27005
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: INSERT INTO ... SELECT
@ 2003-08-13 11:40 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 6+ messages in thread
From: Christoph Haller @ 2003-08-13 11:40 UTC (permalink / raw)
To: pgsql-sql; vwu98034@lycos.com
>
> Coincidentally, I have a very similar case using some twists.
>
> The table I want to insert data is something like
>
> table A (
> user01 int,
> user02 int,
> ...
> primary key (user01, user02),
> CHECK ( user01 < user02 )
> );
>
> And the user table is:
>
> tabe user_table (
> user int constraint pk_user primary key UNIQUE, --PK
> ...,
> email varchar(25) not null,
> ...
> };
>
> In the insertion statement, the user02 is obtained by a subselect
statement: "select user from user where email=?".
>
> There may not exist the email in the user table. And if yes, the check
condition may not be met. If the condition is n
ot met, the two user IDs need to be switched.
>
> How the query shall be construted?
>
> Thanks for any suggestions.
>
I'd say this conditional insert into user02 column can be done by a
PL/pgSQL function
combined by CASE statements.
I'm thinking of something like (untested of course)
INSERT INTO ...
SELECT
CASE WHEN user01 < get_user02(email=?)
THEN user01 ELSE get_user02(email=?) END,
CASE WHEN user01 < get_user02(email=?)
THEN get_user02(email=?) ELSE user01 END,
...
FROM table A ... ;
You might think calling the function four times is a waste,
but as far as I understand it PL/pgSQL functions are run via
prepared execution plans (see PL/pgSQL - SQL Procedural Language -
Overview),
so it shouldn't be too bad.
Don't know what to do about
> There may not exist the email in the user table.
Insert a NULL?
HTH
Regards, Christoph
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: INSERT INTO ... SELECT
@ 2003-08-13 17:14 Vernon Smith <vwu98034@lycos.com>
0 siblings, 0 replies; 6+ messages in thread
From: Vernon Smith @ 2003-08-13 17:14 UTC (permalink / raw)
To: pgsql-sql@postgresql.org, "Christoph Haller" <ch@rodos.fzk.de>
Thanks, Christoph for your thought.
An alternative solution I have is to fetch the user table first and act according with the retured value. It doesn't seem to have a single query solution.
v.
--
--------- Original Message ---------
DATE: Wed, 13 Aug 2003 13:40:53
From: Christoph Haller <ch@rodos.fzk.de>
To: pgsql-sql@postgresql.org, vwu98034@lycos.com
Cc:
>>
>> Coincidentally, I have a very similar case using some twists.
>>
>> The table I want to insert data is something like
>>
>> table A (
>> user01 int,
>> user02 int,
>> ...
>> primary key (user01, user02),
>> CHECK ( user01 < user02 )
>> );
>>
>> And the user table is:
>>
>> tabe user_table (
>> user int constraint pk_user primary key UNIQUE, --PK
>> ...,
>> email varchar(25) not null,
>> ...
>> };
>>
>> In the insertion statement, the user02 is obtained by a subselect
>statement: "select user from user where email=?".
>>
>> There may not exist the email in the user table. And if yes, the check
>condition may not be met. If the condition is n
>ot met, the two user IDs need to be switched.
>>
>> How the query shall be construted?
>>
>> Thanks for any suggestions.
>>
>
>I'd say this conditional insert into user02 column can be done by a
>PL/pgSQL function
>combined by CASE statements.
>I'm thinking of something like (untested of course)
>INSERT INTO ...
>SELECT
>CASE WHEN user01 < get_user02(email=?)
>THEN user01 ELSE get_user02(email=?) END,
>CASE WHEN user01 < get_user02(email=?)
>THEN get_user02(email=?) ELSE user01 END,
>...
>FROM table A ... ;
>
>You might think calling the function four times is a waste,
>but as far as I understand it PL/pgSQL functions are run via
>prepared execution plans (see PL/pgSQL - SQL Procedural Language -
>Overview),
>so it shouldn't be too bad.
>Don't know what to do about
>> There may not exist the email in the user table.
>Insert a NULL?
>
>HTH
>
>Regards, Christoph
>
>
>
____________________________________________________________
Get advanced SPAM filtering on Webmail or POP Mail ... Get Lycos Mail!
http://login.mail.lycos.com/r/referral?aid=27005
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-08-13 17:14 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-11 10:21 INSERT INTO ... SELECT Silke Trissl <trissl@informatik.hu-berlin.de>
2003-08-12 01:57 ` Christopher Browne <cbbrowne@acm.org>
2003-08-12 16:40 ` Jonathan Gardner <jgardner@jonathangardner.net>
2003-08-12 23:06 Re: INSERT INTO ... SELECT Vernon Smith <vwu98034@lycos.com>
2003-08-13 11:40 Re: INSERT INTO ... SELECT Christoph Haller <ch@rodos.fzk.de>
2003-08-13 17:14 Re: INSERT INTO ... SELECT Vernon Smith <vwu98034@lycos.com>
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