Re: MySQL LAST_INSERT_ID() to Postgres

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