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

Re: MySQL LAST_INSERT_ID() to Postgres

From: "Scott Marlowe" <scott(dot)marlowe(at)gmail(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 19:09:03
Message-ID: dcc563d10808281209o5099a45asb3accdea36190e01@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
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.

In response to

Responses

pgsql-general by date

Next:From: Dave PageDate: 2008-08-28 19:11:45
Subject: Re: temp schemas
Previous:From: Scott MarloweDate: 2008-08-28 19:06:55
Subject: Re: importing dates

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