pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL Help - Obtaining ID of record INSERTED
9+ messages / 7 participants
[nested] [flat]
* SQL Help - Obtaining ID of record INSERTED
@ 2005-08-16 21:09 josh@segrestfarms.com <josh@segrestfarms.com>
0 siblings, 4 replies; 9+ messages in thread
From: josh@segrestfarms.com @ 2005-08-16 21:09 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL Help - Obtaining ID of record INSERTED
@ 2005-08-16 21:35 Juan Miguel Paredes <juan.paredes@gmail.com>
parent: josh@segrestfarms.com <josh@segrestfarms.com>
3 siblings, 0 replies; 9+ messages in thread
From: Juan Miguel Paredes @ 2005-08-16 21:35 UTC (permalink / raw)
To: pgsql-admin; +Cc: josh@segrestfarms.com
Greetings, all!
As described in
http://www.postgresql.org/docs/8.0/interactive/datatype.html#DATATYPE-SERIAL,
column "type" SERIAL is really an integer type with an implicit
sequence associated by default with that column. In order to get the
last generated value from the sequence, use the "currval" function, as
described in http://www.postgresql.org/docs/8.0/interactive/functions-sequence.html.
Hope this helps!
On 8/16/05, josh@segrestfarms.com <josh@segrestfarms.com> wrote:
> 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@postgresql.org so that your
> message can get through to the mailing list cleanly
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL Help - Obtaining ID of record INSERTED
@ 2005-08-16 21:42 Jason Minion <jason.minion@sigler.com>
0 siblings, 1 reply; 9+ messages in thread
From: Jason Minion @ 2005-08-16 21:42 UTC (permalink / raw)
To: josh@segrestfarms.com; pgsql-admin
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@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org]On Behalf Of
josh@segrestfarms.com
Sent: Tuesday, August 16, 2005 4:09 PM
To: pgsql-admin@postgresql.org
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@postgresql.org so that your
message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL Help - Obtaining ID of record INSERTED
@ 2005-08-16 22:08 Joshua D. Drake <jd@commandprompt.com>
parent: josh@segrestfarms.com <josh@segrestfarms.com>
3 siblings, 0 replies; 9+ messages in thread
From: Joshua D. Drake @ 2005-08-16 22:08 UTC (permalink / raw)
To: josh@segrestfarms.com <josh@segrestfarms.com>; +Cc: pgsql-admin
josh@segrestfarms.com wrote:
> 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
If you are in the same session you can do:
select currval('sequencename_of_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@postgresql.org so that your
> message can get through to the mailing list cleanly
--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL Help - Obtaining ID of record INSERTED
@ 2005-08-16 22:57 Ben Kim <bkim@coe.tamu.edu>
parent: josh@segrestfarms.com <josh@segrestfarms.com>
3 siblings, 0 replies; 9+ messages in thread
From: Ben Kim @ 2005-08-16 22:57 UTC (permalink / raw)
To: josh@segrestfarms.com <josh@segrestfarms.com>; +Cc: pgsql-admin
You got the suggestion using currval already so here's one alternative.
SELECT NEXTVAL('"orderid_sequence_name"');
Assume this value is $val then do
INSERT INTO orderstemp(orderid,...) VALUES ($val, ...);
I'm not sure if this is generally used, but this works and sometimes this
way is convenient for batch processing.
>SELECT @@identity as orderid
My 0.02$.
Regards,
Ben Kim
Developer
College of Education
Texas A&M University
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL Help - Obtaining ID of record INSERTED
@ 2005-08-17 04:40 Bruno Wolff III <bruno@wolff.to>
parent: josh@segrestfarms.com <josh@segrestfarms.com>
3 siblings, 0 replies; 9+ messages in thread
From: Bruno Wolff III @ 2005-08-17 04:40 UTC (permalink / raw)
To: josh@segrestfarms.com <josh@segrestfarms.com>; +Cc: pgsql-admin
On Tue, Aug 16, 2005 at 17:09:04 -0400,
"josh@segrestfarms.com" <josh@segrestfarms.com> wrote:
> 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.
You want to use currval. In 8.0 you would use:
SELECT currval(pg_get_serial_sequence('orderstemp', 'orderid'));
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL Help - Obtaining ID of record INSERTED
@ 2005-08-17 12:39 josh@segrestfarms.com <josh@segrestfarms.com>
parent: Jason Minion <jason.minion@sigler.com>
0 siblings, 2 replies; 9+ messages in thread
From: josh@segrestfarms.com @ 2005-08-17 12:39 UTC (permalink / raw)
To: Jason Minion <jason.minion@sigler.com>; +Cc: pgsql-admin
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.
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@postgresql.org
>[mailto:pgsql-admin-owner@postgresql.org]On Behalf Of
>josh@segrestfarms.com
>Sent: Tuesday, August 16, 2005 4:09 PM
>To: pgsql-admin@postgresql.org
>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@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL Help - Obtaining ID of record INSERTED
@ 2005-08-17 12:56 Michael Fuhr <mike@fuhr.org>
parent: josh@segrestfarms.com <josh@segrestfarms.com>
1 sibling, 0 replies; 9+ messages in thread
From: Michael Fuhr @ 2005-08-17 12:56 UTC (permalink / raw)
To: josh@segrestfarms.com <josh@segrestfarms.com>; +Cc: Jason Minion <jason.minion@sigler.com>; pgsql-admin
On Wed, Aug 17, 2005 at 08:39:22AM -0400, josh@segrestfarms.com wrote:
> 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.
No, there isn't; or rather, it doesn't matter. See the documentation
and the FAQ.
http://www.postgresql.org/docs/8.0/interactive/functions-sequence.html
Return the value most recently obtained by nextval for this
sequence in the current session. (An error is reported if nextval
has never been called for this sequence in this session.) Notice
that because this is returning a session-local value, it gives a
predictable answer whether or not other sessions have executed
nextval since the current session did.
http://www.postgresql.org/docs/faqs.FAQ.html#4.11.3
4.11.3) Doesn't currval() lead to a race condition with other users?
No. currval() returns the current value assigned by your session,
not by all sessions.
--
Michael Fuhr
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL Help - Obtaining ID of record INSERTED
@ 2005-08-19 05:08 Joshua D. Drake <jd@commandprompt.com>
parent: josh@segrestfarms.com <josh@segrestfarms.com>
1 sibling, 0 replies; 9+ messages in thread
From: Joshua D. Drake @ 2005-08-19 05:08 UTC (permalink / raw)
To: josh@segrestfarms.com <josh@segrestfarms.com>; +Cc: Jason Minion <jason.minion@sigler.com>; pgsql-admin
josh@segrestfarms.com 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@postgresql.org
>> [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of
>> josh@segrestfarms.com
>> Sent: Tuesday, August 16, 2005 4:09 PM
>> To: pgsql-admin@postgresql.org
>> 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@postgresql.org 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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2005-08-19 05:08 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-16 21:09 SQL Help - Obtaining ID of record INSERTED josh@segrestfarms.com <josh@segrestfarms.com>
2005-08-16 21:35 ` Juan Miguel Paredes <juan.paredes@gmail.com>
2005-08-16 22:08 ` Joshua D. Drake <jd@commandprompt.com>
2005-08-16 22:57 ` Ben Kim <bkim@coe.tamu.edu>
2005-08-17 04:40 ` Bruno Wolff III <bruno@wolff.to>
2005-08-16 21:42 Re: SQL Help - Obtaining ID of record INSERTED Jason Minion <jason.minion@sigler.com>
2005-08-17 12:39 ` josh@segrestfarms.com <josh@segrestfarms.com>
2005-08-17 12:56 ` Michael Fuhr <mike@fuhr.org>
2005-08-19 05:08 ` Joshua D. Drake <jd@commandprompt.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox