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

Re: Insert into two tables

From: GH <grasshacker(at)over-yonder(dot)net>
To: Robby Slaughter <webmaster(at)robbyslaughter(dot)com>
Cc: Postgres Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Insert into two tables
Date: 2001-06-19 03:14:52
Message-ID: 20010618221452.A20048@over-yonder.net (view raw or flat)
Thread:
Lists: pgsql-novice
On Mon, Jun 18, 2001 at 04:18:57PM -0500, some SMTP stream spewed forth: 
> I've got two tables, orders and order_items. orders has a primary
> key "ordersid" and order_items has a foreign key, orderid (which
> obviously references orders.ordersid)
> 
> Say I want to create a new order and put some items into it. If
> I use an autoincrement field I could just:
> 
>   INSERT INTO orders VALUES ( ... );
> 
> And then I need to get the orderid I just created to create
> new records in the orderitems table So am I supposed to 
> immediately do a:
> 
>   SELECT ordersid FROM orders ORDER BY ordersid DESC LIMIT 1;

*bonk* What about the transaction that completed before you did the
select...limit?

> And then get the value, and then do inserts in the order items
> table? Surely there's some way to wrap this all up into a 
> nice little procedure or something.

Er, I usually just do this:

select nextval('sequence');
insert into table(id_col) values (<nextval from above>);

That way you are guaranteed a unique sequence value.

You can also do this as:
insert into table (all_columns_except_serial) values (whatever);

> Thanks----
> 
> -Robby


gh


In response to

pgsql-novice by date

Next:From: Nikola MilutinovicDate: 2001-06-19 05:16:20
Subject: Re: Parse error creating tables
Previous:From: Robby SlaughterDate: 2001-06-18 22:54:31
Subject: RE: Insert into two tables

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