X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2765D5281B for ; Fri, 19 Aug 2005 02:08:33 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 27375-10 for ; Fri, 19 Aug 2005 05:08:29 +0000 (GMT) Received: from hosting.commandprompt.com (128.commandprompt.com [207.173.200.128]) by svr1.postgresql.org (Postfix) with ESMTP id EF99352989 for ; Fri, 19 Aug 2005 02:08:28 -0300 (ADT) Received: from [192.168.1.100] (clbb-248.saw.net [64.146.135.248]) (authenticated bits=0) by hosting.commandprompt.com (8.13.4/8.13.4) with ESMTP id j7J56hw6019009; Thu, 18 Aug 2005 22:06:47 -0700 Message-ID: <4305694C.2030102@commandprompt.com> Date: Thu, 18 Aug 2005 22:08:28 -0700 From: "Joshua D. Drake" User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050727) X-Accept-Language: en-us, en MIME-Version: 1.0 To: "josh@segrestfarms.com" Cc: Jason Minion , pgsql-admin@postgresql.org Subject: Re: SQL Help - Obtaining ID of record INSERTED References: <0105A1BF505D304E9E5AF38B63E40E4E807FC2@EXCHANGE.siglercompanies.com> <43032FFA.5070108@segrestfarms.com> In-Reply-To: <43032FFA.5070108@segrestfarms.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (hosting.commandprompt.com [192.168.1.101]); Thu, 18 Aug 2005 22:06:47 -0700 (PDT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.258 required=5 tests=[AWL=-0.212, TO_ADDRESS_EQ_REAL=0.47] X-Spam-Level: X-Archive-Number: 200508/246 X-Sequence-Number: 18565 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('> 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