Thread: Getting primary key from insert statement
Hi, is there a way you can get back the primary key (serialized) from an
insert you just do on a table. For example i have a table called "order" and
i want to get the pid of that record so i can use it in the "orderdetail"
table. It would be nice if that same insert statement could also return the
pid.
Table structure:
create table order (pid serial, orddate date);
create table orderdetail (pid serial, orderpid int, name varchar(50));
Insert statement:
insert into order (date) values ('6/2/1999')
order table
pid | orddate
----------------
1 | 6/2/1999
orderdetail table
pid | orderpid | name
--------------------------
1 | 1 | pencil
2 | 1 | pen
I know there must be a way, but i just couldn't find it in either the pgsql
docs or faq. Thanks in advance,
Thinh
Thus spake Pham, Thinh
> Hi, is there a way you can get back the primary key (serialized) from an
> insert you just do on a table. For example i have a table called "order" and
> i want to get the pid of that record so i can use it in the "orderdetail"
> table. It would be nice if that same insert statement could also return the
> pid.
>
> Table structure:
> create table order (pid serial, orddate date);
You realize that order is a keyword, right?
> Insert statement:
> insert into order (date) values ('6/2/1999')
Assuming you change order to something else, the insert returns the OID
of the record just added. In psql this appears as something like this.
INSERT 36448 1
So, just read back the record.
SELECT pid FROM table WHERE OID = 36448;
The various interfaces give you methods to get this value back. Read the
docs for whichever interface you are using.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
At 00:46 +0300 on 03/06/1999, Pham, Thinh wrote: > Hi, is there a way you can get back the primary key (serialized) from an > insert you just do on a table. For example i have a table called "order" and > i want to get the pid of that record so i can use it in the "orderdetail" > table. It would be nice if that same insert statement could also return the > pid. Yes. You have to know the name of the sequence which the serial type caused to be created. You are notified of the name when you create the table. It would be 'order_pid_seq' in the case you described (remember that ORDER is a reserved word, though). Anyway, after you make the insert, you can retrieve the recently-created value of the sequence, using currval( 'order_pid_seq' ). This returns the value of the last invocation of nextval( 'order_pid_seq' ) in the current session. When you do an insert, it calls nextval internally. And thus you will get a proper currval. The question everybody asks is "but what if somebody else manages to insert another row before I use currval?". This is not a concern, because currval always returns the number your own session generated, and not anybody else. Note that currval will not work until somebody does a nextval on the same sequence. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
[6 Jun 99,, 15:31] Herouth Maoz wrote: > At 00:46 +0300 on 03/06/1999, Pham, Thinh wrote: > > > > Hi, is there a way you can get back the primary key (serialized) from an > > insert you just do on a table. For example i have a table called "order" and [snip] > Yes. You have to know the name of the sequence which the serial type caused > to be created. You are notified of the name when you create the table. It > would be 'order_pid_seq' in the case you described (remember that ORDER is > a reserved word, though). [snip] > Anyway, after you make the insert, you can retrieve the recently-created > value of the sequence, using currval( 'order_pid_seq' ). [snip] > Note that currval will not work until somebody does a nextval on the same > sequence. > Wouldn't it be simpler just to SELECT the next value from the sequence BEFORE the insert and use it in the INSERT statement directly? Emils Klotins e-mail: emils@mail.usis.bkc.lv Systems Manager URL: http://www.usis.bkc.lv/ USIS Riga 7 Smilsu Str., Riga LV1050, LATVIA
At 12:51 +0300 on 07/06/1999, Emils Klotins wrote: > > Wouldn't it be simpler just to SELECT the next value from the sequence >BEFORE the insert and > use it in the INSERT statement directly? Then you have to have a transaction around the two operations. And by the time you do the select, someone may lock the table where you insert. It's a potential for deadlock. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma