josh@xxxxxxxxxxxxxxxx wrote:
Thank you.
You stated that the SELECT currval('orderid_id_seq') statement is a
separate query and must be treated as such.
This gives me cause for concern. My intention is to obtain the
orderid of the order inserted at that moment, but if I am to use a
separate query it would seem there is a chance (albeit a small one)
that another order could come through before I am able to obtain the
orderid.
sequences are transaction/session safe thus it is not a problem. It
would be a problem if *you* in your session inserted another record
or if you disconnected before you ran the currval but as long as you are
in the same session you are fine.
Sincerely,
Joshua D. Drake
Jason Minion wrote:
Check out
http://www.postgresql.org/docs/8.0/interactive/functions-sequence.html
You'll want to use something like "SELECT currval('<name of
sequence>')". The thing
is, your orderid field in orderstemp has a sequence which it uses to
grab the
next value. The currval function called with the name of the sequence
as the
parameter will give you results as you are accustomed to. However, it
is a
separate query and must be treated as such. Note that you need to
call it
using the same database connection. And sequences are special - they
always
update and do not roll back with failed transactions.
If you used default serial notation in your create table statement,
your table is
likely named "orderid_id_seq":
SELECT currval('orderid_id_seq'::text);
Jason
-----Original Message-----
From: pgsql-admin-owner@xxxxxxxxxxxxxx
[mailto:pgsql-admin-owner@xxxxxxxxxxxxxx]On Behalf Of
josh@xxxxxxxxxxxxxxxx
Sent: Tuesday, August 16, 2005 4:09 PM
To: pgsql-admin@xxxxxxxxxxxxxx
Subject: [ADMIN] SQL Help - Obtaining ID of record INSERTED
Greetings.
INSERT INTO
orderstemp (customerid,datecreated)
VALUES
('5443','8/16/2005 12:00PM')
The table orderstemp has a unique identifier field, orderid (of type
SERIAL).
How can I obtain the orderid of the record inserted in the INSERT
INTO statement in postgresql?
MSSQL does it like this:
INSERT INTO
orderstemp (customerid,datecreated)
VALUES
('5443','8/16/2005 12:00PM')
SELECT @@identity as orderid
but this doens't work in postgresql.
Any ideas? Thanks in advance.
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
