From: josh@segrestfarms.com <josh@segrestfarms.com>
To: Jason Minion <jason.minion@sigler.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: SQL Help - Obtaining ID of record INSERTED
Date: Wed, 17 Aug 2005 08:39:22 -0400
Message-ID: <43032FFA.5070108@segrestfarms.com> (raw)
In-Reply-To: <0105A1BF505D304E9E5AF38B63E40E4E807FC2@EXCHANGE.siglercompanies.com>
References: <0105A1BF505D304E9E5AF38B63E40E4E807FC2@EXCHANGE.siglercompanies.com>
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
>
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: josh@segrestfarms.com, jason.minion@sigler.com
Subject: Re: SQL Help - Obtaining ID of record INSERTED
In-Reply-To: <43032FFA.5070108@segrestfarms.com>
References: <0105A1BF505D304E9E5AF38B63E40E4E807FC2@EXCHANGE.siglercompanies.com> <43032FFA.5070108@segrestfarms.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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