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

Re: MySQL LAST_INSERT_ID() to Postgres

From: Steve Atkins <steve(at)blighty(dot)com>
To: pgsql-general General <pgsql-general(at)postgresql(dot)org>
Subject: Re: MySQL LAST_INSERT_ID() to Postgres
Date: 2008-08-28 19:56:44
Message-ID: 8F69EB99-ECE0-4C0D-A267-14B8781DD803@blighty.com (view raw or flat)
Thread:
Lists: pgsql-general
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


In response to

Responses

pgsql-general by date

Next:From: Steve AtkinsDate: 2008-08-28 19:58:51
Subject: Re: SQL optimization - WHERE SomeField STARTING WITH ...
Previous:From: Russ BrownDate: 2008-08-28 19:51:21
Subject: Re: MySQL LAST_INSERT_ID() to Postgres

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