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

Re: MySQL LAST_INSERT_ID() to Postgres

From: Bill <pg(at)dbginc(dot)com>
To: Steve Atkins <steve(at)blighty(dot)com>
Cc: pgsql-general General <pgsql-general(at)postgresql(dot)org>
Subject: Re: MySQL LAST_INSERT_ID() to Postgres
Date: 2008-08-28 21:38:54
Message-ID: 48B71AEE.6030404@dbginc.com (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

Responses

pgsql-general by date

Next:From: Raymond O'DonnellDate: 2008-08-28 21:40:05
Subject: Re: ERROR: relation . . . does not exist
Previous:From: Raymond O'DonnellDate: 2008-08-28 21:37:23
Subject: Re: MySQL LAST_INSERT_ID() to Postgres

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