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

Re: MySQL LAST_INSERT_ID() to Postgres

From: Bill <pg(at)dbginc(dot)com>
To: "Masis, Alexander \(US SSA\)" <alexander(dot)masis(at)baesystems(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: MySQL LAST_INSERT_ID() to Postgres
Date: 2008-08-28 21:26:16
Message-ID: 48B717F8.3070903@dbginc.com (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

Responses

pgsql-general by date

Next:From: Albretch MuellerDate: 2008-08-28 21:29:32
Subject: ERROR: relation . . . does not exist
Previous:From: Scott MarloweDate: 2008-08-28 21:17:07
Subject: Re: MySQL LAST_INSERT_ID() to Postgres

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