Thread: last inserted raw (identity)
Is there an environment variable that returns the
primary key of the last inserted row. This is usefull
if you insert a rwo and need the primary key to insert
it into another table as a foreign key.
In MS-SQL that is equivalent to @@identity variable:
insert into organization ('org name', ....)
insert into contact (@@identity, 'contact name'
.....)
Here Identity is the organization's ID that is needed
as a foreign key in contact table.
Cheers.
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
On Fri, 2003-08-01 at 18:25, b b wrote:
> Is there an environment variable that returns the
> primary key of the last inserted row. This is usefull
> if you insert a rwo and need the primary key to insert
> it into another table as a foreign key.
>
> In MS-SQL that is equivalent to @@identity variable:
> insert into organization ('org name', ....)
> insert into contact (@@identity, 'contact name'
> .....)
> Here Identity is the organization's ID that is needed
> as a foreign key in contact table.
Why "environment variable"? Are you writing this in shell?
--
+-----------------------------------------------------------------+
| Ron Johnson, Jr. Home: ron.l.johnson@cox.net |
| Jefferson, LA USA |
| |
| "I'm not a vegetarian because I love animals, I'm a vegetarian |
| because I hate vegetables!" |
| unknown |
+-----------------------------------------------------------------+
On Fri, Aug 01, 2003 at 04:25:05PM -0700, b b wrote:
>
> Is there an environment variable that returns the
> primary key of the last inserted row. This is usefull
> if you insert a rwo and need the primary key to insert
> it into another table as a foreign key.
>
> In MS-SQL that is equivalent to @@identity variable:
> insert into organization ('org name', ....)
> insert into contact (@@identity, 'contact name'
> .....)
> Here Identity is the organization's ID that is needed
> as a foreign key in contact table.
See currval() and nextval().
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato
Attachment
On Fri, 2003-08-01 at 22:27, Martijn van Oosterhout wrote:
> On Fri, Aug 01, 2003 at 04:25:05PM -0700, b b wrote:
> >
> > Is there an environment variable that returns the
> > primary key of the last inserted row. This is usefull
> > if you insert a rwo and need the primary key to insert
> > it into another table as a foreign key.
> >
> > In MS-SQL that is equivalent to @@identity variable:
> > insert into organization ('org name', ....)
> > insert into contact (@@identity, 'contact name'
> > .....)
> > Here Identity is the organization's ID that is needed
> > as a foreign key in contact table.
>
> See currval() and nextval().
What if his PK isn't a sequence?
--
+-----------------------------------------------------------------+
| Ron Johnson, Jr. Home: ron.l.johnson@cox.net |
| Jefferson, LA USA |
| |
| "I'm not a vegetarian because I love animals, I'm a vegetarian |
| because I hate vegetables!" |
| unknown |
+-----------------------------------------------------------------+
On Fri, Aug 01, 2003 at 10:43:03PM -0500, Ron Johnson wrote:
> On Fri, 2003-08-01 at 22:27, Martijn van Oosterhout wrote:
> > On Fri, Aug 01, 2003 at 04:25:05PM -0700, b b wrote:
> > >
> > > Is there an environment variable that returns the
> > > primary key of the last inserted row. This is usefull
> > > if you insert a rwo and need the primary key to insert
> > > it into another table as a foreign key.
> > >
> > > In MS-SQL that is equivalent to @@identity variable:
> > > insert into organization ('org name', ....)
> > > insert into contact (@@identity, 'contact name'
> > > .....)
> > > Here Identity is the organization's ID that is needed
> > > as a foreign key in contact table.
> >
> > See currval() and nextval().
>
> What if his PK isn't a sequence?
Hadn't thought of that. It would have been part of the insert then, in which
case it may be possible for the application to grab it internally.
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato
Attachment
> > See currval() and nextval(). > > What if his PK isn't a sequence? Moreover, currval() and nextval() won't guarantee that you always get the most recently inserted sequence value, either, because each connection can have a cache of sequence values to assign from. While the backend guarantees that each sequence value will be UNIQUE there is no guarantee that MY currval() or nextval() is actually based on the last sequence value that ANYONE used. In short, I think the answer to the original question is that there is no reliable way to find out what the last record inserted was. -- Mike Nolan
On Fri, Aug 01, 2003 at 11:18:30PM -0500, nolan@celery.tssi.com wrote: > > > See currval() and nextval(). > > > > What if his PK isn't a sequence? > > Moreover, currval() and nextval() won't guarantee that you always get the > most recently inserted sequence value, either, because each connection > can have a cache of sequence values to assign from. While the backend > guarantees that each sequence value will be UNIQUE there is no guarantee > that MY currval() or nextval() is actually based on the last sequence value > that ANYONE used. Rubbish. currval() is guarenteed to return the last value returned by nextval() *in this session*. So if you do a nextval() and sleep for three days with the session open, currval() will return that value even if a million records have been entered in the mean time. > In short, I think the answer to the original question is that there is no > reliable way to find out what the last record inserted was. It returns the last record *you* entered. If you want the last record entered by anyone (committed ofcourse), you'd use order by x desc limit 1. In general, currval() and nextval() do exactly what you need. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > "All that is needed for the forces of evil to triumph is for enough good > men to do nothing." - Edmond Burke > "The penalty good people pay for not being interested in politics is to be > governed by people worse than themselves." - Plato
Attachment
> > In short, I think the answer to the original question is that there is no > > reliable way to find out what the last record inserted was. > > It returns the last record *you* entered. If you want the last record > entered by anyone (committed ofcourse), you'd use order by x desc limit 1. I agree that currval() would return the last record *I* inserted, but I don't understand how an order by clause would always return the last record that *ANYBODY* inserted. (And I thought that was the original question, but perhaps I am mis-remembering it at this point.) If another user has a cache of 10 sequence values, there's no way for me to know that. Thus when the other user inserts a record using one of those cached sequence values, it could be either higher or lower than the sequence value in MY most recently inserted record. (I think it could be higher only if I also have my own cache of sequence values, assuming that the sequence never wraps.) -- Mike Nolan
On Sat, Aug 02, 2003 at 12:22:28AM -0500, nolan@celery.tssi.com wrote: > > > In short, I think the answer to the original question is that there is no > > > reliable way to find out what the last record inserted was. > > > > It returns the last record *you* entered. If you want the last record > > entered by anyone (committed ofcourse), you'd use order by x desc limit 1. > > I agree that currval() would return the last record *I* inserted, but > I don't understand how an order by clause would always return the last > record that *ANYBODY* inserted. (And I thought that was the original > question, but perhaps I am mis-remembering it at this point.) Given he was using @@identity, I'm assuming he needed the id just inserted. > > If another user has a cache of 10 sequence values, there's no way > for me to know that. Thus when the other user inserts a record using > one of those cached sequence values, it could be either higher or > lower than the sequence value in MY most recently inserted record. > (I think it could be higher only if I also have my own cache of sequence > values, assuming that the sequence never wraps.) If you have a cache then the IDs won't match the insert order. You'd use a timestamp if you want actual times to be involved. However, values will only show up once the transactions they were in commit, so finding the the last value inserted right now is not possible. In any case, I beleive currval() is the answer to the original question. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > "All that is needed for the forces of evil to triumph is for enough good > men to do nothing." - Edmond Burke > "The penalty good people pay for not being interested in politics is to be > governed by people worse than themselves." - Plato