Skip site navigation (1) Skip section navigation (2)

Re: [INTERFACES] sql question

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Joseph Shraibman <jks(at)p1(dot)selectacast(dot)net>
Cc: pgsql-interfaces(at)postgreSQL(dot)org
Subject: Re: [INTERFACES] sql question
Date: 1999-12-08 06:40:16
Message-ID: 25795.944635216@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-interfaces
Joseph Shraibman <jks(at)p1(dot)selectacast(dot)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

pgsql-interfaces by date

Next:From: Tom LaneDate: 1999-12-08 06:53:30
Subject: Re: [INTERFACES] locking on database updates
Previous:From: Douglas ThomsonDate: 1999-12-08 04:34:33
Subject: Re: [INTERFACES] locking on database updates

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group