Thread: SERIAL Field
Hi all,
having a table with a serial field what is the corrected method
to retrieve the value after an insert ?
Ciao
Gaetano
--
#exclude <windows>
#include <CSRSS>
printf("\t\t\b\b\b\b\b\b");.
printf("\t\t\b\b\b\b\b\b");
> Hi all,
>
> having a table with a serial field what is the corrected method
> to retrieve the value after an insert ?
Use currval(_name_of_sequence_):
joel=# create table test ( id serial );
NOTICE: CREATE TABLE will create implicit sequence 'test_id_seq' for SERIAL
column 'test.id'
NOTICE: CREATE TABLE / UNIQUE will create implicit index 'test_id_key' for
table 'test'
joel=# insert into test values (default);
INSERT 16617 1
joel=# select currval('test_id_seq');
currval
---------
1
(1 row)
See also nextval() and setval() for other functions for sequences.
Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant
Hi Gaetano, Postgresql implements serial field using sequences. detailed description of sequences are avaliable at http://www.postgresql.org/idocs/index.php?functions-sequence.html a NICE flash tutorial is also available at: http://techdocs.postgresql.org/college/001_sequences/ the specific answer to your question is the function "currval(sequence_name)" can be used. you can get the name of sequenced being used for the serial field by describing the table (\d tablename). But the very idea of inserting the value and then retriving the sequence number sounds fishy to me. Why not get the sequence number first and than insert using that seqnce number (more on the flash tutorial ). regds mallah. On Monday 06 May 2002 04:16 pm, Gaetano Mendola wrote: > Hi all, > > having a table with a serial field what is the corrected method > to retrieve the value after an insert ? > > > > > Ciao > Gaetano -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace.
> -----Original Message----- > From: pgsql-admin-owner@postgresql.org > [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Rajesh Kumar > Mallah. > Sent: Monday, May 06, 2002 9:26 AM > To: Gaetano Mendola; pgsql-admin@postgresql.org > Subject: Re: [ADMIN] SERIAL Field > But the very idea of inserting the value and then retriving the sequence > number sounds fishy to me. Why not get the sequence number first and than > insert using that seqnce number (more on the flash tutorial ). Hmmm... does nextval() 'hold' a sequence number for this backend? Otherwise, the risk would be that you request a value, and someone else has beat you to it before you insert it. In any event, inserting then using currval() is the standard practice around here, and it works great. Nothing fishy at all here, nothing to see, move on. J. Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton Knowledge Management & Technology Consultant
> -----Original Message----- > From: Joel Burton [mailto:joel@joelburton.com] > Sent: Monday, May 06, 2002 10:51 AM > To: Rajesh Kumar Mallah.; Gaetano Mendola; pgsql-admin@postgresql.org > Subject: RE: [ADMIN] SERIAL Field > > > > -----Original Message----- > > From: pgsql-admin-owner@postgresql.org > > [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Rajesh Kumar > > Mallah. > > Sent: Monday, May 06, 2002 9:26 AM > > To: Gaetano Mendola; pgsql-admin@postgresql.org > > Subject: Re: [ADMIN] SERIAL Field > > > But the very idea of inserting the value and then retriving the > sequence > > number sounds fishy to me. Why not get the sequence number > first and than > > insert using that seqnce number (more on the flash tutorial ). > > Hmmm... does nextval() 'hold' a sequence number for this backend? > Otherwise, the risk would be that you request a value, and > someone else has beat you to it before you insert it. Duh. Of course it does. Forgive me: it's still a bit early here in EST for my brain to be on. > In any event, inserting then using currval() is the standard > practice around here, and it works great. Nothing fishy at all > here, nothing to see, move on. > > J. > > Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton > Knowledge Management & Technology Consultant
On 6 May 2002 at 10:51, Joel Burton wrote:
> > But the very idea of inserting the value and then retriving the sequence
> > number sounds fishy to me. Why not get the sequence number first and than
> > insert using that seqnce number (more on the flash tutorial ).
That's pretty standard in a number of database applications (i.e. not just
PostgreSQL situations).
> Hmmm... does nextval() 'hold' a sequence number for this backend?
> Otherwise, the risk would be that you request a value, and someone else has
> beat you to it before you insert it.
I use nextval all the time. In fact:
fp2migration=# \d users
Column | Type | Modifiers
------------+--------------------------+--------------------------
id | integer | not null default nextval('users_id_seq'::text)
What's wrong this this? Nobody is going to "beat me to it".
> In any event, inserting then using currval() is the standard practice
> around here, and it works great. Nothing fishy at all here, nothing to see,
> move on.
Why is that "less risk"?
--
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples
> -----Original Message----- > From: Dan Langille [mailto:dan@langille.org] > Sent: Monday, May 06, 2002 12:41 PM > To: Joel Burton > Cc: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] SERIAL Field > > > In any event, inserting then using currval() is the standard practice > > around here, and it works great. Nothing fishy at all here, > nothing to see, > > move on. > > Why is that "less risk"? It's not; nextval() is just fine. I was half asleep and thinking, I think, of another database when I wrote that. "You say nextval() + write, I say write + currval()..." J. Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton Knowledge Management & Technology Consultant
On 6 May 2002 at 12:43, Joel Burton wrote: > > -----Original Message----- > > From: Dan Langille [mailto:dan@langille.org] > > Sent: Monday, May 06, 2002 12:41 PM > > To: Joel Burton > > Cc: pgsql-admin@postgresql.org > > Subject: Re: [ADMIN] SERIAL Field > > > > > In any event, inserting then using currval() is the standard practice > > > around here, and it works great. Nothing fishy at all here, > > nothing to see, > > > move on. > > > > Why is that "less risk"? > > It's not; nextval() is just fine. I was half asleep and thinking, I think, > of another database when I wrote that. > > "You say nextval() + write, I say write + currval()..." So long as a given application does not mix the two approaches, everything should be fine. -- Dan Langille The FreeBSD Diary - http://freebsddiary.org/ - practical examples
My personal experience is that you MUST operate like: SELECT nextval... INSERT ... I have several web sites that have many simultaneous updates occurring, based on user interaction with the website and its underlying data structures. I used to INSERT, then SELECT currval, and depending on timing, I might get a different sequence number than the one I actually used. Apparently I had the following occurring: INSERT ... INSERT ... SELECT currval... SELECT currval... In this situation, both of the SELECTs return the same sequence number. So basically, user1 would update user2's "myarea" data. This problem became much more critical when I went to multiple Apache servers on the front end and had a higher likelihood of concurrency. However, even on a single server, you can't guarantee where the task switch will occur and mess you up. - brian On Mon, 6 May 2002, Joel Burton wrote: > > > -----Original Message----- > > From: Dan Langille [mailto:dan@langille.org] > > Sent: Monday, May 06, 2002 12:41 PM > > To: Joel Burton > > Cc: pgsql-admin@postgresql.org > > Subject: Re: [ADMIN] SERIAL Field > > > > > In any event, inserting then using currval() is the standard practice > > > around here, and it works great. Nothing fishy at all here, > > nothing to see, > > > move on. > > > > Why is that "less risk"? > > It's not; nextval() is just fine. I was half asleep and thinking, I think, > of another database when I wrote that. > > "You say nextval() + write, I say write + currval()..." > > J. > > Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton > Knowledge Management & Technology Consultant > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > Wm. Brian McCane | Life is full of doors that won't open Search http://recall.maxbaud.net/ | when you knock, equally spaced amid those Usenet http://freenews.maxbaud.net/ | that open when you don't want them to. Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"
> > "You say nextval() + write, I say write + currval()..." > >So long as a given application does not mix the two approaches, everything >should be fine. Though, if you have multiple clients inserting into the table, there is a chance that between client A inserting the record and then calling currval(), client B could have performed an insert, in which case client A gets the value of client B's record. 'nextval() + write' avoids this possible overlap. -- Joel Stevenson Bear River Associates, Inc.
Joel Stevenson <jstevenson@bearriver.com> writes: > Though, if you have multiple clients inserting into the table, there > is a chance that between client A inserting the record and then > calling currval(), client B could have performed an insert, in which > case client A gets the value of client B's record. 'nextval() + > write' avoids this possible overlap. People who have not studied the documentation frequently make that claim. *There is no such risk* (unless A and B are interleaving their commands on a single database connection, in which case they have lots worse risks of conflicts than this one). See http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/functions-sequence.html regards, tom lane
> >People who have not studied the documentation frequently make that >claim. *There is no such risk* (unless A and B are interleaving their >commands on a single database connection, in which case they have lots >worse risks of conflicts than this one). See >http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/functions-sequence.html > > regards, tom lane Point taken. I've worked on web-apps which use a single JDBC connection per user session where the overlap could occur, but as you say there are far greater issues involved for such session-based connections. -- Joel Stevenson Bear River Associates, Inc.