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

Re: MySQL LAST_INSERT_ID() to Postgres

From: "D(dot) Dante Lorenso" <dante(at)lorenso(dot)com>
To: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
Cc: Bill <pg(at)dbginc(dot)com>, Steve Atkins <steve(at)blighty(dot)com>, pgsql-general General <pgsql-general(at)postgresql(dot)org>
Subject: Re: MySQL LAST_INSERT_ID() to Postgres
Date: 2008-08-28 22:23:36
Message-ID: 48B72568.7010102@lorenso.com (view raw or flat)
Thread:
Lists: pgsql-general
Scott Marlowe wrote:
> On Thu, Aug 28, 2008 at 3:38 PM, Bill <pg(at)dbginc(dot)com> wrote:
>> I am new to PostgreSQL but it seems to me that lastval() will only work if
>> the insert does not produce side effects that call nextval(). Consider the
>> case where a row is inserted into a table that has an after insert trigger
>> and the after insert trigger inserts a row into another table which has a
>> serial primary key. In that case I assume that lastval() will  return the
>> value from the serial column in the second table.
> 
> I use returning almost exclusively now.

RETURNING is the best option.  It makes all your INSERT and UPDATE 
statements feel like SELECTs.  It avoids the round-trip back to the 
server just to ask for the unique id generated by the previous statement.

   INSERT INTO mytable (col1, col2)
   VALUES (value1, value2)
   RETURNING col_value_from_seq_that_we_dont_care_about_the_name;

I use RETURNING for all my insert and UPDATE statements now.  Usually 
I'll return the primary key for the table, but sometimes I return a 
column that is created by one of my triggers.  It's awesome to be able 
to do this in one query.

-- Dante


In response to

Responses

pgsql-general by date

Next:From: Matthew DennisDate: 2008-08-28 22:25:49
Subject: Re: indexes on functions and create or replace function
Previous:From: Alvaro HerreraDate: 2008-08-28 22:22:46
Subject: Re: MySQL LAST_INSERT_ID() to Postgres

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