To: pgsql-sql@postgresql.org, "Christopher Browne" <cbbrowne@acm.org>
Date: Tue, 12 Aug 2003 19:06:33 -0400
From: "Vernon Smith" <vwu98034@lycos.com>
Message-ID: <CEJHJKMINCCMFCAA@mailcity.com>
Mime-Version: 1.0
Reply-To: vwu98034@lycos.com
Subject: Re: INSERT INTO ... SELECT
Organization: Lycos Mail  (http://www.mail.lycos.com:80)
Content-Type: text/plain; charset=us-ascii
Content-Language: en
Content-Transfer-Encoding: 7bit

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