Re: LAST_INSERT_ID equivalent

While many others use currval(), we tend to grab the next ID provided by
nextval('seq') and use that to be inserted with the record. The process
is very atomic, and the ID is available to be used by the rest of your
program. The only drawback is if your insert query fails there will be a
hole in the sequence.

- Ericson Smith

Bruno Wolff III wrote:

>On Thu, Jun 12, 2003 at 13:44:16 -0400,
> Erik Price <eprice(at)ptc(dot)com> wrote:
>
>
>>Edmund Dengler wrote:
>>
>>
>>>Greetings all!
>>>
>>>I believe
>>> select currval('sequence_name');
>>>should satisfy your needs. Within a transaction it will stay the same.
>>>
>>>
>>Ed, thanks, this looks like what I was looking for --
>>
>>however, I am concerned by your disclaimer. Can you explain that a
>>little bit? I read it to mean "if you try to use this technique within
>>a transaction where you are INSERTing a new record, it will not reflect
>>the new record's ID". So then in order to determine the new record's ID
>>I would need to use
>>
>> SELECT CURRVAL('sequence_name') + 1;
>>
>>within the transaction.
>>
>>
>
>No. You just want to use currval. The comment was referring to other
>transactions calling nextval while the transaction of interest is
>proceeding.
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>message can get through to the mailing list cleanly
>
>