Re: Referencing serial col's sequence for insert

On Tue, Jul 22, 2014 at 9:46 AM, Anil Menon <gakmenon(at)gmail(dot)com> wrote:

> Am a bit confused -which one comes first?
>
> 1) the 'data'||currval('id01_col1_seq') is parsed first : which means it
> takes the current session's currval
> 2) then the insert is attempted which causes a sequence.nextval to be
> performed which means that 'data'||currval('id01_col1_seq')will be
> different from the sequence's value
>
>
​If this was the case currval would always emit an error for the first
insert of the session...​

or
>
> 1) an insert is attempted which causes a sequence.nextval to be performed
> and then
> 2) the cols are parsed for the insert so the 'data'||currval('id01_col1_seq')
> has the correct value
>
> I observe the latter on my single session notebook instance of postgres.
>
>
​And given that it is the logical conclusion why are you confused?​

​To be honest I totally missed the dual-column nature of the OP. I read it
as simply wishing to use the sequence value in a string instead of, not in
addition to, the "serial" defined column.

I do not know whether the call to nextval in the default will always occur
before any currval expression in the source query...it might make more
sense, for multiple reasons, to simply define a trigger to enforce the
value of "col2". A user-defined trigger will always be evaluated after the
default expression and so you can simply pick off the value assigned to
"col1" and do what you'd like with it. Combined with a constraint you can
remove the entire business rule from user logic and embed it into the
database where it cannot be messed up.

David J.​