Re: MySQL LAST_INSERT_ID() to Postgres

I was mapping C++ application code that works with mySQL to work with
Postgres.
There were a number of articles on line regarding the conversion from
mySQL to Postgres like:
http://en.wikibooks.org/wiki/Programming:Converting_MySQL_to_PostgreSQL

http://groups.drupal.org/node/4680

http://jmz.iki.fi/blog/programming/converting_mysql_database_to_postgres
ql

http://www.raditha.com/blog/archives/000488.html

However, I found the most difficult issue was related to a MySQL's
"SELECT LAST_INSERT_ID()" sql call.
If your code did not use LAST_INSERT_ID(), then you don't have to read
this post.
In MySQL "LAST_INSERT_ID()" is a MySQL's syntax that returns the
last auto_increment type ID of the row(record) inserted in a table.

In other words, if your MySQL table had a auto_increment
datatype for a field, that field will automatically advance whenever a
new record(row) is inserted into that table.

It is sometimes handy to know what is the value of that ID, that
has just been added to the table, so that that record(row) can be
addressed/updated later.

Well, in MySQL it's easy you just do:
"SELECT LAST_INSERT_ID();"
In Postgres, however it is not that simple. You have to know the
name of so called "insert sequence". Postgres has a system function for
that( SQL line below ).
In Postgres you will have to provide the table and column name(
"auto_increment" type in MySQL or "serial or bigserial" in Postgres).

Here is that SQL query that returns the last inserted ID:

"SELECT CURRVAL(
pg_get_serial_sequence('my_tbl_name','id_col_name'));"

Alexander Masis.

On Thu, Aug 28, 2008 at 10:14 AM, Masis, Alexander (US SSA)
<alexander(dot)masis(at)baesystems(dot)com> wrote:
> I was mapping C++ application code that works with mySQL to work with
> Postgres.
> There were a number of articles on line regarding the conversion from
> mySQL to Postgres like:
SNIP
> Well, in MySQL it's easy you just do:
> "SELECT LAST_INSERT_ID();"
> In Postgres, however it is not that simple. You have to know the
> name of so called "insert sequence". Postgres has a system function for
> that( SQL line below ).
> In Postgres you will have to provide the table and column name(
> "auto_increment" type in MySQL or "serial or bigserial" in Postgres).
>
> Here is that SQL query that returns the last inserted ID:
>
> "SELECT CURRVAL(
> pg_get_serial_sequence('my_tbl_name','id_col_name'));"

That's the hard way. Starting with pgsql 8.2 you can do it much more easily:

create table tester (id serial primary key, info text);
insert into tester (info) values ('this is a text string') returning id;

tada! All done, that insert will return the id for you.

Masis, Alexander (US SSA) wrote:
>
> "SELECT CURRVAL(
> pg_get_serial_sequence('my_tbl_name','id_col_name'));"
>

Any reason why you can't just do this?

CREATE FUNCTION last_insert_id() RETURNS bigint AS $$
SELECT lastval();
$$ LANGUAGE SQL VOLATILE;

On Aug 28, 2008, at 12:09 PM, Scott Marlowe wrote:

> On Thu, Aug 28, 2008 at 10:14 AM, Masis, Alexander (US SSA)
> <alexander(dot)masis(at)baesystems(dot)com> wrote:
>> I was mapping C++ application code that works with mySQL to work with
>> Postgres.
>> There were a number of articles on line regarding the conversion from
>> mySQL to Postgres like:
> SNIP
>> Well, in MySQL it's easy you just do:
>> "SELECT LAST_INSERT_ID();"
>> In Postgres, however it is not that simple. You have to know
>> the
>> name of so called "insert sequence". Postgres has a system function
>> for
>> that( SQL line below ).
>> In Postgres you will have to provide the table and column name(
>> "auto_increment" type in MySQL or "serial or bigserial" in Postgres).
>>
>> Here is that SQL query that returns the last inserted ID:
>>
>> "SELECT CURRVAL(
>> pg_get_serial_sequence('my_tbl_name','id_col_name'));"
>
> That's the hard way. Starting with pgsql 8.2 you can do it much
> more easily:
>
> create table tester (id serial primary key, info text);
> insert into tester (info) values ('this is a text string') returning
> id;
>
> tada! All done, that insert will return the id for you.

Or lastval() if you want something bug-compatible with MySQL.

Cheers,
Steve

On Thu, Aug 28, 2008 at 1:56 PM, Steve Atkins <steve(at)blighty(dot)com> wrote:
>
> Or lastval() if you want something bug-compatible with MySQL.

Not exactly. LAST_INSERT_ID is transactionally safe in that one
connection doesn't see another connections. However, it has it's own
special brand of bug that to me, is much worse.

create table test (id int auto_increment primary key);
insert into test values (DEFAULT);
select LAST_INSERT_ID();
1

In two sessions:
S1: insert into test values (DEFAULT);
S1: select LAST_INSERT_ID();
2
S2: insert into test values (DEFAULT);
S2: select LAST_INSERT_ID();
3
S1: select LAST_INSERT_ID();
2

So that seems reasonable. But here's the part that makes me go huh?

insert into test values (DEFAULT),(DEFAULT),(DEFAULT);
select * from test;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+----+
select LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 3 |
+------------------+

uh, what? It returns not the LAST insert from a multicolumn insert
but the first. Correct me if I'm wrong, but 3 is most certainly NOT
the last id inserted by our session.

Try the same thing in postgresql and you get the much less
pathological and more understandable set returned:

insert into test values (DEFAULT),(DEFAULT),(DEFAULT) returning i;
i
---
6
7
8

And if it was a BIG insert, and interleaved with another big insert so
it got every other ID, you'd get something back like 6,8,10,11,14
etc... so you'd know again, exactly which records you'd created.

Masis, Alexander (US SSA) wrote:
> I was mapping C++ application code that works with mySQL to work with
> Postgres.
> There were a number of articles on line regarding the conversion from
> mySQL to Postgres like:
> http://en.wikibooks.org/wiki/Programming:Converting_MySQL_to_PostgreSQL
>
> http://groups.drupal.org/node/4680
>
> http://jmz.iki.fi/blog/programming/converting_mysql_database_to_postgres
> ql
>
> http://www.raditha.com/blog/archives/000488.html
>
> However, I found the most difficult issue was related to a MySQL's
> "SELECT LAST_INSERT_ID()" sql call.
> If your code did not use LAST_INSERT_ID(), then you don't have to read
> this post.
> In MySQL "LAST_INSERT_ID()" is a MySQL's syntax that returns the
> last auto_increment type ID of the row(record) inserted in a table.
>
> In other words, if your MySQL table had a auto_increment
> datatype for a field, that field will automatically advance whenever a
> new record(row) is inserted into that table.
>
> It is sometimes handy to know what is the value of that ID, that
> has just been added to the table, so that that record(row) can be
> addressed/updated later.
>
> Well, in MySQL it's easy you just do:
> "SELECT LAST_INSERT_ID();"
> In Postgres, however it is not that simple. You have to know the
> name of so called "insert sequence". Postgres has a system function for
> that( SQL line below ).
> In Postgres you will have to provide the table and column name(
> "auto_increment" type in MySQL or "serial or bigserial" in Postgres).
>
> Here is that SQL query that returns the last inserted ID:
>
> "SELECT CURRVAL(
> pg_get_serial_sequence('my_tbl_name','id_col_name'));"
>
>
> Alexander Masis.
>
>
>
>
That will work reliably in a multi-user environment if and only if
currval() returns the last value for the current connection. I assume
this is the case but the description of currval() in the PostgreSQL
documentation says "Return value most recently obtained with |nextval|
for specified sequence". There is no mention that currval() returns the
last value obtained by calling nextval() for the current connection. Can
someone confirm that currval() returns the the value for the connection
from which it is called?

Bill

On 28/08/2008 22:26, Bill wrote:

> someone confirm that currval() returns the the value for the connection
> from which it is called?

Yes, see here:

http://www.postgresql.org/docs/8.3/static/functions-sequence.html

and specifically a little further down the page on "currval":

Return the value most recently obtained by nextval for
this sequence in the current session. (An error is reported
if nextval has never been called for this sequence in this
session.) Notice that because this is returning a session-local
value, it gives a predictable answer whether or not other
sessions have executed nextval since the current session did.

HTH,

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod(at)iol(dot)ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

Steve Atkins wrote:
>
> On Aug 28, 2008, at 12:09 PM, Scott Marlowe wrote:
>
>> On Thu, Aug 28, 2008 at 10:14 AM, Masis, Alexander (US SSA)
>> <alexander(dot)masis(at)baesystems(dot)com> wrote:
>>> I was mapping C++ application code that works with mySQL to work with
>>> Postgres.
>>> There were a number of articles on line regarding the conversion from
>>> mySQL to Postgres like:
>> SNIP
>>> Well, in MySQL it's easy you just do:
>>> "SELECT LAST_INSERT_ID();"
>>> In Postgres, however it is not that simple. You have to know the
>>> name of so called "insert sequence". Postgres has a system function for
>>> that( SQL line below ).
>>> In Postgres you will have to provide the table and column name(
>>> "auto_increment" type in MySQL or "serial or bigserial" in Postgres).
>>>
>>> Here is that SQL query that returns the last inserted ID:
>>>
>>> "SELECT CURRVAL(
>>> pg_get_serial_sequence('my_tbl_name','id_col_name'));"
>>
>> That's the hard way. Starting with pgsql 8.2 you can do it much more
>> easily:
>>
>> create table tester (id serial primary key, info text);
>> insert into tester (info) values ('this is a text string') returning id;
>>
>> tada! All done, that insert will return the id for you.
>
> Or lastval() if you want something bug-compatible with MySQL.
>
> Cheers,
> Steve
>
>
I am new to PostgreSQL but it seems to me that lastval() will only work
if the insert does not produce side effects that call nextval().
Consider the case where a row is inserted into a table that has an after
insert trigger and the after insert trigger inserts a row into another
table which has a serial primary key. In that case I assume that
lastval() will return the value from the serial column in the second table.

Bill

On Thu, Aug 28, 2008 at 3:38 PM, Bill <pg(at)dbginc(dot)com> wrote:
> I am new to PostgreSQL but it seems to me that lastval() will only work if
> the insert does not produce side effects that call nextval(). Consider the
> case where a row is inserted into a table that has an after insert trigger
> and the after insert trigger inserts a row into another table which has a
> serial primary key. In that case I assume that lastval() will return the
> value from the serial column in the second table.

No, setval, currval, and lastval all require as an argument a sequence
name. So the real issue is you have to know the sequence name to use
them.

The problem with lastval is that it reports the last value that the
sequence gave out whether it was to us or someone else. this makes it
NOT SAFE for concurrent transactions, but more for maintenance work.

I use returning almost exclusively now.

On Thu, 28 Aug 2008 16:06:14 -0600
"Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> wrote:

> On Thu, Aug 28, 2008 at 3:38 PM, Bill <pg(at)dbginc(dot)com> wrote:
> > I am new to PostgreSQL but it seems to me that lastval() will only
> > work if the insert does not produce side effects that call
> > nextval(). Consider the case where a row is inserted into a table
> > that has an after insert trigger and the after insert trigger
> > inserts a row into another table which has a serial primary key. In
> > that case I assume that lastval() will return the value from the
> > serial column in the second table.
>
> No, setval, currval, and lastval all require as an argument a sequence
> name. So the real issue is you have to know the sequence name to use
> them.

lastval() does not take a sequence name.

Joshua D. Drake

--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

Russ Brown escribió:
> Masis, Alexander (US SSA) wrote:
> >
> > "SELECT CURRVAL(
> > pg_get_serial_sequence('my_tbl_name','id_col_name'));"
>
> Any reason why you can't just do this?
>
> CREATE FUNCTION last_insert_id() RETURNS bigint AS $$
> SELECT lastval();
> $$ LANGUAGE SQL VOLATILE;

If your table has a trigger that inserts into another table with its own
sequence, you're screwed.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Scott Marlowe wrote:
> On Thu, Aug 28, 2008 at 3:38 PM, Bill <pg(at)dbginc(dot)com> wrote:
>> I am new to PostgreSQL but it seems to me that lastval() will only work if
>> the insert does not produce side effects that call nextval(). Consider the
>> case where a row is inserted into a table that has an after insert trigger
>> and the after insert trigger inserts a row into another table which has a
>> serial primary key. In that case I assume that lastval() will return the
>> value from the serial column in the second table.
>
> I use returning almost exclusively now.

RETURNING is the best option. It makes all your INSERT and UPDATE
statements feel like SELECTs. It avoids the round-trip back to the
server just to ask for the unique id generated by the previous statement.

INSERT INTO mytable (col1, col2)
VALUES (value1, value2)
RETURNING col_value_from_seq_that_we_dont_care_about_the_name;

I use RETURNING for all my insert and UPDATE statements now. Usually
I'll return the primary key for the table, but sometimes I return a
column that is created by one of my triggers. It's awesome to be able
to do this in one query.

-- Dante

On Aug 28, 2008, at 3:23 PM, D. Dante Lorenso wrote:
> I use RETURNING for all my insert and UPDATE statements now.
> Usually I'll return the primary key for the table, but sometimes I
> return a column that is created by one of my triggers. It's
> awesome to be able to do this in one query.
>

Word. My current pet architecture is to set up Postgres like an
application server (the web front end just call PL/pgSQL stuff rather
than doing direct SQL), and this makes my life much easier for that.

Scott Marlowe wrote:
> On Thu, Aug 28, 2008 at 3:38 PM, Bill <pg(at)dbginc(dot)com> wrote:
>
>> I am new to PostgreSQL but it seems to me that lastval() will only work if
>> the insert does not produce side effects that call nextval(). Consider the
>> case where a row is inserted into a table that has an after insert trigger
>> and the after insert trigger inserts a row into another table which has a
>> serial primary key. In that case I assume that lastval() will return the
>> value from the serial column in the second table.
>>
>
> No, setval, currval, and lastval all require as an argument a sequence
> name. So the real issue is you have to know the sequence name to use
> them.
>
> The problem with lastval is that it reports the last value that the
> sequence gave out whether it was to us or someone else. this makes it
> NOT SAFE for concurrent transactions, but more for maintenance work.
>
> I use returning almost exclusively now.
>
>
The PostgresSQL 8.3 help file clearly shows that lastval() does not take
a sequence as a parameter and the description i is "Return the value
most recently returned by |nextval| in the current session. This
function is identical to |currval|, except that instead of taking the
sequence name as an argument it fetches the value of the last sequence
that |nextval| was used on in the current session. It is an error to
call |lastval| if |nextval| has not yet been called in the current
session." Is the help incorrect?

Bill

On Thu, 28 Aug 2008 15:29:51 -0700
Bill <pg(at)dbginc(dot)com> wrote:

> The PostgresSQL 8.3 help file clearly shows that lastval() does not
> take a sequence as a parameter and the description i is "Return the
> value most recently returned by |nextval| in the current session.
> This function is identical to |currval|, except that instead of
> taking the sequence name as an argument it fetches the value of the
> last sequence that |nextval| was used on in the current session. It
> is an error to call |lastval| if |nextval| has not yet been called in
> the current session." Is the help incorrect?

No.

Joshua D. Drake

>
> Bill

--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

On Thu, Aug 28, 2008 at 4:18 PM, Joshua Drake <jd(at)commandprompt(dot)com> wrote:
> On Thu, 28 Aug 2008 16:06:14 -0600
> "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> wrote:
>> No, setval, currval, and lastval all require as an argument a sequence
>> name. So the real issue is you have to know the sequence name to use
>> them.
>
> lastval() does not take a sequence name.

Sorry, haven't used lastval before, was making an incorrect assumption about it.

On Thu, 28 Aug 2008 16:46:19 -0600
"Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> wrote:

> On Thu, Aug 28, 2008 at 4:18 PM, Joshua Drake <jd(at)commandprompt(dot)com>
> wrote:
> > On Thu, 28 Aug 2008 16:06:14 -0600
> > "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> wrote:
> >> No, setval, currval, and lastval all require as an argument a
> >> sequence name. So the real issue is you have to know the sequence
> >> name to use them.
> >
> > lastval() does not take a sequence name.
>
> Sorry, haven't used lastval before, was making an incorrect
> assumption about it.
>

FYI, I wouldn't use it either.

Joshua D. Drake

--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

"Raymond O'Donnell" <rod(at)iol(dot)ie> writes:
> On 28/08/2008 22:26, Bill wrote:
>> someone confirm that currval() returns the the value for the connection
>> from which it is called?

> Yes, see here:
> http://www.postgresql.org/docs/8.3/static/functions-sequence.html
> and specifically a little further down the page on "currval":

A general comment on those pages is that the tabular lists of functions
are intended to give one-liner descriptions of what the functions do.
For cases where a one-liner isn't sufficient, there's a sentence or a
paragraph following the table.

I don't find this layout remarkably intuitive myself, but I wonder
whether anyone has a concrete plan for making it better?

regards, tom lane

Alvaro Herrera wrote:
> Russ Brown escribió:
>> Masis, Alexander (US SSA) wrote:
>>> "SELECT CURRVAL(
>>> pg_get_serial_sequence('my_tbl_name','id_col_name'));"
>> Any reason why you can't just do this?
>>
>> CREATE FUNCTION last_insert_id() RETURNS bigint AS $$
>> SELECT lastval();
>> $$ LANGUAGE SQL VOLATILE;
>
> If your table has a trigger that inserts into another table with its own
> sequence, you're screwed.

I assume you're equally screwed with MySQL LAST_INSERT_ID() in that case
- so it'd be bug compatible.

I know MSSQL had similar issues with the use of @@IDENTITY - which is
why they went down the ugly path of SCOPE_IDENTITY(). Why they didn't
just implement RETURNING, I have no idea...

//Magnus

Magnus Hagander escribió:
> Alvaro Herrera wrote:
> > Russ Brown escribió:
> >> Masis, Alexander (US SSA) wrote:
> >>> "SELECT CURRVAL(
> >>> pg_get_serial_sequence('my_tbl_name','id_col_name'));"
> >> Any reason why you can't just do this?
> >>
> >> CREATE FUNCTION last_insert_id() RETURNS bigint AS $$
> >> SELECT lastval();
> >> $$ LANGUAGE SQL VOLATILE;
> >
> > If your table has a trigger that inserts into another table with its own
> > sequence, you're screwed.
>
> I assume you're equally screwed with MySQL LAST_INSERT_ID() in that case
> - so it'd be bug compatible.

Yeah, which is another reason not to use triggers; more pileups for the
whole "new features are there just for checklist's sake" argument.

The approach proposed by Alexander above does not have such problem,
which is why it is better than the alternative suggested by Russ.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On 29/08/2008 05:45, Tom Lane wrote:

> A general comment on those pages is that the tabular lists of functions
> are intended to give one-liner descriptions of what the functions do.
> For cases where a one-liner isn't sufficient, there's a sentence or a
> paragraph following the table.
>
> I don't find this layout remarkably intuitive myself, but I wonder
> whether anyone has a concrete plan for making it better?

Maybe a simple "see further below" or the like after the one-liner?

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod(at)iol(dot)ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

--- On Thu, 8/28/08, Masis, Alexander (US SSA) <alexander(dot)masis(at)baesystems(dot)com> wrote:

> From: Masis, Alexander (US SSA) <alexander(dot)masis(at)baesystems(dot)com>
> Subject: [GENERAL] MySQL LAST_INSERT_ID() to Postgres
> To: pgsql-general(at)postgresql(dot)org
> Date: Thursday, August 28, 2008, 4:14 PM
> I was mapping C++ application code that works with mySQL to
> work with
> Postgres.
> There were a number of articles on line regarding the
> conversion from
> mySQL to Postgres like:
> http://en.wikibooks.org/wiki/Programming:Converting_MySQL_to_PostgreSQL
>
> http://groups.drupal.org/node/4680
>
> http://jmz.iki.fi/blog/programming/converting_mysql_database_to_postgres
> ql
>
> http://www.raditha.com/blog/archives/000488.html
>
> However, I found the most difficult issue was related to a
> MySQL's
> "SELECT LAST_INSERT_ID()" sql call.
> If your code did not use LAST_INSERT_ID(), then you
> don't have to read
> this post.
> In MySQL "LAST_INSERT_ID()" is a MySQL's
> syntax that returns the
> last auto_increment type ID of the row(record) inserted in
> a table.
>
> In other words, if your MySQL table had a auto_increment
> datatype for a field, that field will automatically advance
> whenever a
> new record(row) is inserted into that table.
>
> It is sometimes handy to know what is the value of that
> ID, that
> has just been added to the table, so that that record(row)
> can be
> addressed/updated later.
>

use insert into.....returning val1,val2.....

http://www.postgresql.org/docs/8.3/static/sql-insert.html

this can return the value of the sequence of the table

> Well, in MySQL it's easy you just do:
> "SELECT LAST_INSERT_ID();"
> In Postgres, however it is not that simple. You have to
> know the
> name of so called "insert sequence". Postgres has
> a system function for
> that( SQL line below ).
> In Postgres you will have to provide the table and column
> name(
> "auto_increment" type in MySQL or "serial or
> bigserial" in Postgres).
>
> Here is that SQL query that returns the last inserted ID:
>
> "SELECT CURRVAL(
> pg_get_serial_sequence('my_tbl_name','id_col_name'));"
>
>
> Alexander Masis.
>
>
>
> --
> Sent via pgsql-general mailing list
> (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general