Thread: How do I select the last Id in a column???
Hello,
I'm trying to find out how I select the last id in a
column if I don't know anything else about the row.
What i am doing is inserting information into my
Orders table and the id is auto-incrementing. The
next thing i want to be able to do is grab the the id
of the order i just inserted. How do i do this?
Here is the code i have so far:
my $sql = "INSERT INTO ${Orders}
(locationid,shippingid,statusid,date) VALUES (?
,?,?,?)";
my $sth = $dbh->prepare($sql);
$sth->execute(${LocationID},${ShippingID},${StatusID},${date});
#--*************************************
my $sth = $dbh->prepare("SELECT id FROM ${Orders}");
$sth->execute();
$OrderID = $sth->fetchrow_arrayref;
I know the select statement that i have will not work,
because it will bring me every id number in the table.
I just want the latest one.....
Please HELP............
Thank you so much,
Mike
__________________________________
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com
to get the last record inserted just
select * from row order by autoincId desc limit 1;
if you have several clients doing this you might want to put your insert and
the select inside a transaction.
----- Original Message -----
From: "Michael Kovalcik" <makd32@yahoo.com>
To: <pgsql-admin@postgresql.org>
Sent: Monday, June 23, 2003 11:47 AM
Subject: [ADMIN] How do I select the last Id in a column???
> Hello,
>
> I'm trying to find out how I select the last id in a
> column if I don't know anything else about the row.
> What i am doing is inserting information into my
> Orders table and the id is auto-incrementing. The
> next thing i want to be able to do is grab the the id
> of the order i just inserted. How do i do this?
>
> Here is the code i have so far:
>
> my $sql = "INSERT INTO ${Orders}
> (locationid,shippingid,statusid,date) VALUES (?
> ,?,?,?)";
>
> my $sth = $dbh->prepare($sql);
>
> $sth->execute(${LocationID},${ShippingID},${StatusID},${date});
> #--*************************************
> my $sth = $dbh->prepare("SELECT id FROM ${Orders}");
>
> $sth->execute();
> $OrderID = $sth->fetchrow_arrayref;
>
> I know the select statement that i have will not work,
> because it will bring me every id number in the table.
> I just want the latest one.....
>
> Please HELP............
>
> Thank you so much,
> Mike
>
> __________________________________
> Do you Yahoo!?
> SBC Yahoo! DSL - Now only $29.95 per month!
> http://sbc.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
To get the ID after the fact:
SELECT currval('my_seq') AS id;
To get the ID before even inserting it
SELECT nextval('my_seq') AS id;
- Ericson Smith
eric@did-it.com
Josh Goldberg wrote:
>to get the last record inserted just
>select * from row order by autoincId desc limit 1;
>
>if you have several clients doing this you might want to put your insert and
>the select inside a transaction.
>
>----- Original Message -----
>From: "Michael Kovalcik" <makd32@yahoo.com>
>To: <pgsql-admin@postgresql.org>
>Sent: Monday, June 23, 2003 11:47 AM
>Subject: [ADMIN] How do I select the last Id in a column???
>
>
>
>
>>Hello,
>>
>>I'm trying to find out how I select the last id in a
>>column if I don't know anything else about the row.
>>What i am doing is inserting information into my
>>Orders table and the id is auto-incrementing. The
>>next thing i want to be able to do is grab the the id
>>of the order i just inserted. How do i do this?
>>
>>Here is the code i have so far:
>>
>>my $sql = "INSERT INTO ${Orders}
>>(locationid,shippingid,statusid,date) VALUES (?
>>,?,?,?)";
>>
>>my $sth = $dbh->prepare($sql);
>>
>>$sth->execute(${LocationID},${ShippingID},${StatusID},${date});
>>#--*************************************
>>my $sth = $dbh->prepare("SELECT id FROM ${Orders}");
>>
>>$sth->execute();
>>$OrderID = $sth->fetchrow_arrayref;
>>
>>I know the select statement that i have will not work,
>>because it will bring me every id number in the table.
>> I just want the latest one.....
>>
>>Please HELP............
>>
>>Thank you so much,
>>Mike
>>
>>__________________________________
>>Do you Yahoo!?
>>SBC Yahoo! DSL - Now only $29.95 per month!
>>http://sbc.yahoo.com
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 2: you can get off all lists at once with the unregister command
>> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>>
>>
>>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
The proper way to do this is basically this:
begin;
insert into table test values ('abc',123);
select currval('sequsedbytest');
insert into dependent_table values (keyfromabovecurrval,'moredata',456);
commit;
On Mon, 23 Jun 2003, Michael Kovalcik wrote:
> Hello,
>
> I'm trying to find out how I select the last id in a
> column if I don't know anything else about the row.
> What i am doing is inserting information into my
> Orders table and the id is auto-incrementing. The
> next thing i want to be able to do is grab the the id
> of the order i just inserted. How do i do this?
>
> Here is the code i have so far:
>
> my $sql = "INSERT INTO ${Orders}
> (locationid,shippingid,statusid,date) VALUES (?
> ,?,?,?)";
>
> my $sth = $dbh->prepare($sql);
>
> $sth->execute(${LocationID},${ShippingID},${StatusID},${date});
> #--*************************************
> my $sth = $dbh->prepare("SELECT id FROM ${Orders}");
>
> $sth->execute();
> $OrderID = $sth->fetchrow_arrayref;
>
> I know the select statement that i have will not work,
> because it will bring me every id number in the table.
> I just want the latest one.....
>
> Please HELP............
>
> Thank you so much,
> Mike
>
> __________________________________
> Do you Yahoo!?
> SBC Yahoo! DSL - Now only $29.95 per month!
> http://sbc.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
What we do since 2 queries are necessary anyway, is to select the
NEXTVAL which gives us our 'record id' up front. Then when we do our
insert, we include that field with the value we just selected from our
sequence and we're all set. Of course if the insert fails for some
reason, then you will have a gap in the IDs, but that is not a problem
to us and the query to perform the NEXTVAL is surely less expensive than
a query on the table after the insert to get the ID.
Josh Goldberg wrote:
>
> to get the last record inserted just
> select * from row order by autoincId desc limit 1;
>
> if you have several clients doing this you might want to put your insert and
> the select inside a transaction.
>
> ----- Original Message -----
> From: "Michael Kovalcik" <makd32@yahoo.com>
> To: <pgsql-admin@postgresql.org>
> Sent: Monday, June 23, 2003 11:47 AM
> Subject: [ADMIN] How do I select the last Id in a column???
>
> > Hello,
> >
> > I'm trying to find out how I select the last id in a
> > column if I don't know anything else about the row.
> > What i am doing is inserting information into my
> > Orders table and the id is auto-incrementing. The
> > next thing i want to be able to do is grab the the id
> > of the order i just inserted. How do i do this?
> >
> > Here is the code i have so far:
> >
> > my $sql = "INSERT INTO ${Orders}
> > (locationid,shippingid,statusid,date) VALUES (?
> > ,?,?,?)";
> >
> > my $sth = $dbh->prepare($sql);
> >
> > $sth->execute(${LocationID},${ShippingID},${StatusID},${date});
> > #--*************************************
> > my $sth = $dbh->prepare("SELECT id FROM ${Orders}");
> >
> > $sth->execute();
> > $OrderID = $sth->fetchrow_arrayref;
> >
> > I know the select statement that i have will not work,
> > because it will bring me every id number in the table.
> > I just want the latest one.....
> >
> > Please HELP............
> >
> > Thank you so much,
> > Mike
> >
> > __________________________________
> > Do you Yahoo!?
> > SBC Yahoo! DSL - Now only $29.95 per month!
> > http://sbc.yahoo.com
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
Bill MacArthur
Webmaster
DHS Club
On Thu, Jun 26, 2003 at 14:00:33 -0700, Josh Goldberg <josh@4dmatrix.com> wrote: > to get the last record inserted just > select * from row order by autoincId desc limit 1; > > if you have several clients doing this you might want to put your insert and > the select inside a transaction. That won't help. Your suggestion will only work in serializable transactions. In read committed mode the select might see a value for autoincId from a transaction that committed between the insert and the select.
From what Bruno enlightened us to about read commit mode, it seems this is
the only safe way to go.
Wouldn't currval (as presented by others) be affected in the same manner as
my example?
----- Original Message -----
From: "DHS Webmaster" <webmaster@dhs-club.com>
To: "Michael Kovalcik" <makd32@yahoo.com>
Cc: <pgsql-admin@postgresql.org>
Sent: Thursday, June 26, 2003 2:37 PM
Subject: Re: [ADMIN] How do I select the last Id in a column???
> What we do since 2 queries are necessary anyway, is to select the
> NEXTVAL which gives us our 'record id' up front. Then when we do our
> insert, we include that field with the value we just selected from our
> sequence and we're all set. Of course if the insert fails for some
> reason, then you will have a gap in the IDs, but that is not a problem
> to us and the query to perform the NEXTVAL is surely less expensive than
> a query on the table after the insert to get the ID.
>
> Josh Goldberg wrote:
> >
> > to get the last record inserted just
> > select * from row order by autoincId desc limit 1;
> >
> > if you have several clients doing this you might want to put your insert
and
> > the select inside a transaction.
> >
> > ----- Original Message -----
> > From: "Michael Kovalcik" <makd32@yahoo.com>
> > To: <pgsql-admin@postgresql.org>
> > Sent: Monday, June 23, 2003 11:47 AM
> > Subject: [ADMIN] How do I select the last Id in a column???
> >
> > > Hello,
> > >
> > > I'm trying to find out how I select the last id in a
> > > column if I don't know anything else about the row.
> > > What i am doing is inserting information into my
> > > Orders table and the id is auto-incrementing. The
> > > next thing i want to be able to do is grab the the id
> > > of the order i just inserted. How do i do this?
> > >
> > > Here is the code i have so far:
> > >
> > > my $sql = "INSERT INTO ${Orders}
> > > (locationid,shippingid,statusid,date) VALUES (?
> > > ,?,?,?)";
> > >
> > > my $sth = $dbh->prepare($sql);
> > >
> > > $sth->execute(${LocationID},${ShippingID},${StatusID},${date});
> > > #--*************************************
> > > my $sth = $dbh->prepare("SELECT id FROM ${Orders}");
> > >
> > > $sth->execute();
> > > $OrderID = $sth->fetchrow_arrayref;
> > >
> > > I know the select statement that i have will not work,
> > > because it will bring me every id number in the table.
> > > I just want the latest one.....
> > >
> > > Please HELP............
> > >
> > > Thank you so much,
> > > Mike
> > >
> > > __________________________________
> > > Do you Yahoo!?
> > > SBC Yahoo! DSL - Now only $29.95 per month!
> > > http://sbc.yahoo.com
> > >
> > > ---------------------------(end of
broadcast)---------------------------
> > > TIP 2: you can get off all lists at once with the unregister command
> > > (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)
> > >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
> --
> Bill MacArthur
> Webmaster
> DHS Club
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
On Thu, Jun 26, 2003 at 17:46:14 -0700, Josh Goldberg <josh@4dmatrix.com> wrote: > From what Bruno enlightened us to about read commit mode, it seems this is > the only safe way to go. > > Wouldn't currval (as presented by others) be affected in the same manner as > my example? No because currval returns the last value used in the current session.
What could cause a table to act serialized when read committed transactions are set in the configuration? That is something I am running into, which provoked my [incorrect] example. From: "Bruno Wolff III" <bruno@wolff.to> Sent: Thursday, June 26, 2003 4:30 PM > On Thu, Jun 26, 2003 at 14:00:33 -0700, > Josh Goldberg <josh@4dmatrix.com> wrote: > > to get the last record inserted just > > select * from row order by autoincId desc limit 1; > > > > if you have several clients doing this you might want to put your insert and > > the select inside a transaction. > > That won't help. Your suggestion will only work in serializable transactions. > > In read committed mode the select might see a value for autoincId from > a transaction that committed between the insert and the select. > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend >
All, i got the answer i was needing. I used the following: select max(id) from table it works great!! :) --- Josh Goldberg <josh@4dmatrix.com> wrote: > What could cause a table to act serialized when read > committed transactions > are set in the configuration? That is something I > am running into, which > provoked my [incorrect] example. > > From: "Bruno Wolff III" <bruno@wolff.to> > Sent: Thursday, June 26, 2003 4:30 PM > > On Thu, Jun 26, 2003 at 14:00:33 -0700, > > Josh Goldberg <josh@4dmatrix.com> wrote: > > > to get the last record inserted just > > > select * from row order by autoincId desc limit > 1; > > > > > > if you have several clients doing this you might > want to put your insert > and > > > the select inside a transaction. > > > > That won't help. Your suggestion will only work in > serializable > transactions. > > > > In read committed mode the select might see a > value for autoincId from > > a transaction that committed between the insert > and the select. > > > > ---------------------------(end of > broadcast)--------------------------- > > TIP 8: explain analyze is your friend > > > __________________________________ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com
Note that that has race conditions, i.e. two clients operating at the same time could get the same max(id). The setval currval and nextval functions exist to prevent race conditions, and they work well. Rolling your own is a recipe for disaster. On Fri, 27 Jun 2003, Michael Kovalcik wrote: > All, > > i got the answer i was needing. I used the following: > > select max(id) from table > > it works great!! :) > > --- Josh Goldberg <josh@4dmatrix.com> wrote: > > What could cause a table to act serialized when read > > committed transactions > > are set in the configuration? That is something I > > am running into, which > > provoked my [incorrect] example. > > > > From: "Bruno Wolff III" <bruno@wolff.to> > > Sent: Thursday, June 26, 2003 4:30 PM > > > On Thu, Jun 26, 2003 at 14:00:33 -0700, > > > Josh Goldberg <josh@4dmatrix.com> wrote: > > > > to get the last record inserted just > > > > select * from row order by autoincId desc limit > > 1; > > > > > > > > if you have several clients doing this you might > > want to put your insert > > and > > > > the select inside a transaction. > > > > > > That won't help. Your suggestion will only work in > > serializable > > transactions. > > > > > > In read committed mode the select might see a > > value for autoincId from > > > a transaction that committed between the insert > > and the select. > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > > TIP 8: explain analyze is your friend > > > > > > > > __________________________________ > Do you Yahoo!? > SBC Yahoo! DSL - Now only $29.95 per month! > http://sbc.yahoo.com > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
On Fri, Jun 27, 2003 at 10:06:41 -0700, Josh Goldberg <josh@4dmatrix.com> wrote: > What could cause a table to act serialized when read committed transactions > are set in the configuration? That is something I am running into, which > provoked my [incorrect] example. Getting lucky. Did you actually run two transactions in parallel and stop between the select and insert so that you could do a select, insert and commit in the other transaction?
I did, and the second transaction wouldn't even complete the insert until the first transaction commit'd or rollback'd. I created two new tables and tried on there and it produced the expected behaviour, but several of my existing tables do not. I changed my code to use currval from now on, just to play it safe :-) ----- Original Message ----- From: "Bruno Wolff III" <bruno@wolff.to> To: "Josh Goldberg" <josh@4dmatrix.com> Cc: "Michael Kovalcik" <makd32@yahoo.com>; <pgsql-admin@postgresql.org> Sent: Friday, June 27, 2003 4:48 PM Subject: Re: [ADMIN] How do I select the last Id in a column??? > On Fri, Jun 27, 2003 at 10:06:41 -0700, > Josh Goldberg <josh@4dmatrix.com> wrote: > > What could cause a table to act serialized when read committed transactions > > are set in the configuration? That is something I am running into, which > > provoked my [incorrect] example. > > Getting lucky. Did you actually run two transactions in parallel and stop > between the select and insert so that you could do a select, insert and > commit in the other transaction? > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >