Thread: Multi table insert
Hello,
I have created a multi-table insert script in python, the problem is that this worked fine on an older PostgreSQL db (7.2) but now I get an
Error, psycopg2.ProgrammingError: missing FROM-clause entry for table "business_name_business_name_seq"
Here is part of the script
It is a Z SQL Method for Zope.
##########
INSERT INTO business_name (business_name, business_url, business_type_id)
values (<dtml-sqlvar business_name type="string" >, <dtml-sqlvar business_url type="string">, <dtml-sqlvar business_type_list_id type="int">);
<dtml-var sql_delimiter>
INSERT INTO business_address (street_name, town, city, postcode, county_id, business_name_id) values
(<dtml-sqlvar street_name type="string">,
<dtml-sqlvar town type="string">,
<dtml-sqlvar city type="string">,
<dtml-sqlvar postcode type="string">,
<dtml-sqlvar county_list type="int">,
business_name_business_name_seq.last_value);
#########
Here I want to take the last_value for the business_name_id that was generated in the first insert and put it as the related item in the next INSERT table.
Is there a better way to do this? I have 4 tables all related and one input form.
Many thanks
Norman
All new Yahoo! Mail "The new Interface is stunning in its simplicity and ease of use." - PC Magazine
I have created a multi-table insert script in python, the problem is that this worked fine on an older PostgreSQL db (7.2) but now I get an
Error, psycopg2.ProgrammingError: missing FROM-clause entry for table "business_name_business_name_seq"
Here is part of the script
It is a Z SQL Method for Zope.
##########
INSERT INTO business_name (business_name, business_url, business_type_id)
values (<dtml-sqlvar business_name type="string" >, <dtml-sqlvar business_url type="string">, <dtml-sqlvar business_type_list_id type="int">);
<dtml-var sql_delimiter>
INSERT INTO business_address (street_name, town, city, postcode, county_id, business_name_id) values
(<dtml-sqlvar street_name type="string">,
<dtml-sqlvar town type="string">,
<dtml-sqlvar city type="string">,
<dtml-sqlvar postcode type="string">,
<dtml-sqlvar county_list type="int">,
business_name_business_name_seq.last_value);
#########
Here I want to take the last_value for the business_name_id that was generated in the first insert and put it as the related item in the next INSERT table.
Is there a better way to do this? I have 4 tables all related and one input form.
Many thanks
Norman
All new Yahoo! Mail "The new Interface is stunning in its simplicity and ease of use." - PC Magazine
am Fri, dem 27.10.2006, um 13:07:34 +0100 mailte norman folgendes: > Hello, > I have created a multi-table insert script in python, the problem is that this > worked fine on an older PostgreSQL db (7.2) but now I get an > > Error, psycopg2.ProgrammingError: missing FROM-clause entry for table > "business_name_business_name_seq" > > Here is part of the script > > It is a Z SQL Method for Zope. I'm not familar with Zope, but the error is clear, this is a new behavior since 8.x. As a fast work around you can set add_missing_from to on in your postgresql.conf. More infos: http://developer.postgresql.org/pgdocs/postgres/runtime-config-compatible.html Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
can you replace: business_name_business_name_seq.last_value with: (select last_value from business_name_business_name_seq) or, more defensively coded: (select max(last_value) from business_name_business_name_seq) i am assuming business_name_business_name_seq is some kind of one-row table that is always guaranteed (how?) to have one row and sort of fakes a sequence (which brings the question of why not use a true sequence?) > -----Original Message----- > From: pgsql-novice-owner@postgresql.org > [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of norman > Sent: Friday, October 27, 2006 5:08 AM > To: pgsql-novice@postgresql.org > Subject: [NOVICE] Multi table insert > > Hello, > I have created a multi-table insert script in python, the > problem is that this worked fine on an older PostgreSQL db > (7.2) but now I get an > > Error, psycopg2.ProgrammingError: missing FROM-clause entry > for table "business_name_business_name_seq" > > Here is part of the script > > It is a Z SQL Method for Zope. > > ########## > > > INSERT INTO business_name (business_name, business_url, > business_type_id) > values (<dtml-sqlvar business_name type="string" >, > <dtml-sqlvar business_url type="string">, <dtml-sqlvar > business_type_list_id type="int">); > > <dtml-var sql_delimiter> > > INSERT INTO business_address (street_name, town, city, > postcode, county_id, business_name_id) values > (<dtml-sqlvar street_name type="string">, > <dtml-sqlvar town type="string">, > <dtml-sqlvar city type="string">, > <dtml-sqlvar postcode type="string">, > <dtml-sqlvar county_list type="int">, > business_name_business_name_seq.last_value); > > ######### > > Here I want to take the last_value for the business_name_id > that was generated in the first insert and put it as the > related item in the next INSERT table. > > Is there a better way to do this? I have 4 tables all related > and one input form. > > Many thanks > > Norman >
Hi and thanks for your replies.
The fix was as per Andreas' post and this works now.
Although, I would like to improve the code and make it better.
The ' business_name_business_name_seq' is just a sequence type for the table business_name where business_name table is related like:
business_name -> business_address by the business_name_id
as the submit form is just one form, i inserted the values in order so I needed the fk_business_name_id to be taken from the previous insert and so on...
perhaps there is better way to do this.
cheers
norman
George Pavlov <gpavlov@mynewplace.com> wrote:
The fix was as per Andreas' post and this works now.
Although, I would like to improve the code and make it better.
The ' business_name_business_name_seq' is just a sequence type for the table business_name where business_name table is related like:
business_name -> business_address by the business_name_id
as the submit form is just one form, i inserted the values in order so I needed the fk_business_name_id to be taken from the previous insert and so on...
perhaps there is better way to do this.
cheers
norman
George Pavlov <gpavlov@mynewplace.com> wrote:
can you replace:
business_name_business_name_seq.last_value
with:
(select last_value from business_name_business_name_seq)
or, more defensively coded:
(select max(last_value) from business_name_business_name_seq)
i am assuming business_name_business_name_seq is some kind of one-row
table that is always guaranteed (how?) to have one row and sort of fakes
a sequence (which brings the question of why not use a true sequence?)
> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of norman
> Sent: Friday, October 27, 2006 5:08 AM
> To: pgsql-novice@postgresql.org
> Subject: [NOVICE] Multi table insert
>
> Hello,
> I have created a multi-table insert script in python, the
> problem is that this worked fine on an older PostgreSQL db
> (7.2) but now I get an
>
> Error, psycopg2.ProgrammingError: missing FROM-clause entry
> for table "business_name_business_name_seq"
>
> Here is part of the script
>
> It is a Z SQL Method for Zope.
>
> ##########
>
>
> INSERT INTO business_name (business_name, business_url,
> business_type_id)
> values (,
> ,
> business_type_list_id type="int">);
>
>
>
> INSERT INTO business_address (street_name, town, city,
> postcode, county_id, business_name_id) values
> (,
> ,
> ,
> ,
> ,
> business_name_business_name_seq.last_value);
>
> #########
>
> Here I want to take the last_value for the business_name_id
> that was generated in the first insert and put it as the
> related item in the next INSERT table.
>
> Is there a better way to do this? I have 4 tables all related
> and one input form.
>
> Many thanks
>
> Norman
>
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Send instant messages to your online friends http://uk.messenger.yahoo.com