Skip site navigation (1) Skip section navigation (2)

Re: inputs for pg_get_id() function?

From: "Richard Broersma Jr(dot)" <rabroersma(at)yahoo(dot)com>
To: Steve Lefevre <lefevre(dot)10(at)osu(dot)edu>, pgsql-novice(at)postgresql(dot)org
Subject: Re: inputs for pg_get_id() function?
Date: 2007-06-14 05:06:17
Message-ID: 4670CCC9.1050807@yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
 > My current understanding of postgres is that I need to know what the 
id is *before* I do the insert into the parent table.

you can use curval('sequence_name') to get the id of the newly assigned 
row.  This function actually isolates the current value for connection 
to the database.  This way, your current value is not confused with 
someone elses current value.  Also, if you are using PostgreSQL 8.2, 
there is a newly added PostgreSQL specific extenstion that you can use:

INSERT INTO your_table ( pkey, col1, col2 ) values ( DEFAULT, 1, 
'hello') RETURNING pkey;

This statement will perform the insert and return your primary key in 
one statement.  check out the last example from:
http://www.postgresql.org/docs/8.2/interactive/sql-insert.html#AEN52425

> But my understanding of Postgres, from earlier conversations on this 
> mailing list, is that using something like "SELECT last_value FROM 
> sequence" doesn't mean that I will get the id from the insert I just 
> did -- instead I will get the highest value from the sequence. In a 
> high traffic situation, there may have been an insert that happened 
> just after mine, and I would get that id instead of the one that 
> resulted from my insert. That is, the sequence is subject to race 
> conditions.
>
If you use nextval() you are protected from these kind of collisions. No 
matter how many concurrent users you have hammering a way at your 
database with calls to nextval(), you are always protected from id 
collisions.

> So I want to make sure that the line items I insert get related to the 
> invoice row I just inserted, not the necessarily the last value of the 
> sequence.
> Of course, I could just make the function take the name of the 
> sequence as the sole input. But the name of the sequence is arbitrary, 
> no? 
if you want you can create you own sequence or sequences with any name 
that you want to give it.  Next as an option, you can manually alter 
your table to use your newly created sequence as the default value.  
Lastly as another option, you can alter your sequence so that it is 
owned by your table.

> So while I could assume that it would be  table_field_seq, because 
> that is automatically created when you specify a serial column, that 
> is not necessarily the name of the sequence.
> When I'm programming, I don't want to have to double-check the name of 
> the pkey sequence of the table I'm dealing with ( although I do have 
> it created automatically). I just want to refer to the table, whose 
> name I'm already certain of. I'd like to have it automated, so that 
> the function look up the name of the sequence of the primary key, 
> rather than me having to know it. I think I could get away with
>
There probably is a sql statement that will retrieve the sequence from a 
given table,  however, I'm not not fortunite enough to know what it is. :-(

I am sure that other on IRC or the PG generals mailing list would have 
this answer.  Also, besure to CC all replies to the mailing list so the 
others can also participle.

Regards,
Richard Broersma Jr.


In response to

pgsql-novice by date

Next:From: Richard Broersma Jr.Date: 2007-06-14 05:22:44
Subject: Re: no results for nextval() query?
Previous:From: Robert WimmerDate: 2007-06-13 20:53:16
Subject: Re: Mapping one to many

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group