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

Re: Subselect query for a multi table insert single query

From: "Norman Khine" <norman(at)khine(dot)net>
To: "Pgsql-Novice" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Subselect query for a multi table insert single query
Date: 2002-09-23 04:07:43
Message-ID: 004e01c262b6$c4e977c0$0100a8c0@alpha (view raw or flat)
Thread:
Lists: pgsql-novice
 Hi Tom,
 Thanks for this, would the sql be something like

 INSERT INTO business_address (business_name_id) select
 currval('business_name_business_name_seq');

 Is this correct? If so, how do you Insert the other values that are not
 associated with the sequence i.e address, town etc....

 can i do something like

 INSERT INTO business_address (street_name, town, county, postcode,
 county_id, business_name_id) values (<dtml-sqlvar street_name
 type="string">, <dtml-sqlvar town type="string">, <dtml-sqlvar county
 type="string">, <dtml-sqlvar postcode
 type="string">, <dtml-sqlvar county_id type="int">, select
 currval('business_name_business_name_seq');

 This does not work?!As I get a parse error near "select"

 What should be the correct syntax for embedding this.

 Thanks

 Norm
 ----- Original Message -----
> From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> To: "Norman Khine" <norman(at)khine(dot)net>
> Cc: "Pgsql-Novice" <pgsql-novice(at)postgresql(dot)org>
> Sent: Sunday, September 22, 2002 11:03 PM
> Subject: Re: [NOVICE] Subselect query for a multi table insert single
query
>
>
> > "Norman Khine" <norman(at)khine(dot)net> writes:
> > > 1) INSERT INTO business_name (business_name, business_url)
> > >   values ('<dtml-var business_name>', '<dtml-var business_url>');
> >
> > > 2) select last_value from business_name_business_name_seq
> >
> > Instead use
> >
> > select currval('business_name_business_name_seq');
> >
> > to get the assigned sequence value without a race condition.  See
> >
>
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/functions-sequen
> ce.html
> >
> > Actually you don't need to bother with the separate select, unless
> > your client code needs that ID for other purposes.  You could just
> > write the currval() call in the second INSERT.
> >
> > regards, tom lane
> >
>


Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2002-09-23 04:18:54
Subject: Re: Subselect query for a multi table insert single query
Previous:From: Thomas_FaheyDate: 2002-09-23 02:08:58
Subject: writing to external file

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