Search Postgresql Archives

Re: MySQL LAST_INSERT_ID() to Postgres

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

 





--- On Thu, 8/28/08, Masis, Alexander (US SSA) <alexander.masis@xxxxxxxxxxxxxx> wrote:

> From: Masis, Alexander (US SSA) <alexander.masis@xxxxxxxxxxxxxx>
> Subject: [GENERAL] MySQL LAST_INSERT_ID() to Postgres
> To: pgsql-general@xxxxxxxxxxxxxx
> 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@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


      



[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