Search Postgresql Archives

Re: MySQL LAST_INSERT_ID() to Postgres

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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.masis@xxxxxxxxxxxxxx> 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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux