Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
	by hub.org (8.9.3/8.9.3) with ESMTP id BAA12751
	for <pgsql-interfaces@postgreSQL.org>;
	Wed, 8 Dec 1999 01:41:20 -0500 (EST)
	(envelope-from tgl@sss.pgh.pa.us)
Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1])
	by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id BAA25797;
	Wed, 8 Dec 1999 01:40:16 -0500 (EST)
To: Joseph Shraibman <jks@p1.selectacast.net>
cc: pgsql-interfaces@postgreSQL.org
Subject: Re: [INTERFACES] sql question 
In-reply-to: Your message of Tue, 07 Dec 1999 21:14:37 -0500
	<384DBF0D.DF9C29AB@selectacast.net>
Date: Wed, 08 Dec 1999 01:40:16 -0500
Message-ID: <25795.944635216@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Joseph Shraibman <jks@p1.selectacast.net> writes:
> Is there any way to get something like this into valid sql?  Postgres
> doesn't like this:

> INSERT INTO application (key, creator, name) values ( 1 ,(select userkey
> from user where username = 'b' ), 'newapplication') ;

FWIW, this works in current sources and will be in the next release
(6.6 or 7.0 or whatever...).  6.5 doesn't support sub-selects in
target lists, which is what you have here.  You could work around it
with something like

INSERT INTO application SELECT 1, userkey, 'newapplication' FROM
user WHERE username = 'b';

although this doesn't mean quite the same thing (if there are multiple
user rows with username = 'b', the former raises an error while the
latter inserts multiple rows into application).

BTW: I dunno what version you are running, but "user" has been a
reserved word at least since 6.5.  Might be good to rename your table
before upgrading.

			regards, tom lane