--- 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
