X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0F12E52983 for ; Wed, 17 Aug 2005 09:40:31 -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 75693-09 for ; Wed, 17 Aug 2005 12:40:25 +0000 (GMT) Received: from mail.segrestfarms.com (unknown [65.124.195.241]) by svr1.postgresql.org (Postfix) with SMTP id 35D215293D for ; Wed, 17 Aug 2005 09:40:20 -0300 (ADT) Received: (qmail 30225 invoked by uid 399); 17 Aug 2005 12:38:16 -0000 Received: from unknown (HELO njord.segrestfarms.com) (65.124.195.227) by 65-124-195-241.dia.cust.qwest.net with SMTP; 17 Aug 2005 12:38:16 -0000 Received: from [192.100.100.176] (unknown [192.100.100.176]) by njord.segrestfarms.com (Postfix) with ESMTP id 5049280E524; Wed, 17 Aug 2005 14:09:14 -0400 (EDT) Message-ID: <43032FFA.5070108@segrestfarms.com> Date: Wed, 17 Aug 2005 08:39:22 -0400 From: "josh@segrestfarms.com" User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Jason Minion Cc: pgsql-admin@postgresql.org Subject: Re: SQL Help - Obtaining ID of record INSERTED References: <0105A1BF505D304E9E5AF38B63E40E4E807FC2@EXCHANGE.siglercompanies.com> In-Reply-To: <0105A1BF505D304E9E5AF38B63E40E4E807FC2@EXCHANGE.siglercompanies.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=2.803 required=5 tests=[AWL=-0.012, FORGED_RCVD_HELO=0.05, RCVD_IN_DSBL=2.765] X-Spam-Level: ** X-Archive-Number: 200508/214 X-Sequence-Number: 18533 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('')". 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 > > > >