Re: MySQL LAST_INSERT_ID() to Postgres

--- On Thu, 8/28/08, Masis, Alexander (US SSA) <alexander(dot)masis(at)baesystems(dot)com> wrote:

> From: Masis, Alexander (US SSA) <alexander(dot)masis(at)baesystems(dot)com>
> Subject: [GENERAL] MySQL LAST_INSERT_ID() to Postgres
> To: pgsql-general(at)postgresql(dot)org
> Date: Thursday, August 28, 2008, 4:14 PM
> 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.
>

use insert into.....returning val1,val2.....

http://www.postgresql.org/docs/8.3/static/sql-insert.html

this can return the value of the sequence of the table

> 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.
>
>
>
> --
> Sent via pgsql-general mailing list
> (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general