From: Vernon Smith <vwu98034@lycos.com>
To: pgsql-sql@postgresql.org, "Christopher Browne" <cbbrowne@acm.org>
Subject: Re: INSERT INTO ... SELECT
Date: Tue, 12 Aug 2003 19:06:33 -0400
Message-ID: <CEJHJKMINCCMFCAA@mailcity.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: vwu98034@lycos.com, cbbrowne@acm.org
Subject: Re: INSERT INTO ... SELECT
In-Reply-To: <CEJHJKMINCCMFCAA@mailcity.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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