Re: [GENERAL] sequence used on null value or get the max value for a column whith concurrency
On Thu, 2017-08-10 at 19:05 -0300, marcelo wrote:
> In some table, I have a bigint column which at the app level can be
> null. Call it "DocumentNumber", and of course is not the PK.
> In most cases, the applications give some value to the column.
>
> But sometimes, the value remains null, expecting the backend or
> someone
> assign it a unique value.
>
> Could I use a sequence only when the field arrives to the backend as
> null? How? Using a triger?
>
> Alternatively:
>
> How could I get the max value for the column and increment it by
> one,
> but with concurrency warranty? Something as a table lock?
>
> TIA
>
>
>
>
>
Hello Marcelo,
I haven't tested this but if you define the column thus:-
document_number bigint default
nextval('my_document_number_sequence'::regclass)
then on insert, if that column is not in the values list, then the next
available number from the sequence will be used.
Remember, that if a rollback occurs, the sequence number is lost
forever.
HTH,
Robert
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] sequence used on null value or get the max value for a column whith concurrency
Thinking aloud... On Thu, Aug 10, 2017 at 3:05 PM, marcelo wrote: > In some table, I have a bigint column which at the app level can be null. > Call it "DocumentNumber", and of course is not the PK. > In most cases, the applications give some value to the column. > > But sometimes, the value remains null, expecting the backend or someone > assign it a unique value. > > Could I use a sequence only when the field arrives to the backend as null? > How? Using a triger? > Can you reserve a portion of the value range for auto-generated numbers that application-assigned values will take on? If so it would reasonably simple to invoke nextval() in a trigger. How could I get the max value for the column and increment it by one, but > with concurrency warranty? Something as a table lock? > One option would be to maintain the value in a separate table that you update on insert using "UPDATE tbl SET col = col + 1 RETURNING col INTO new_doc_num" You could probably make it an unlogged table as well and you'd return from the trigger function with new_doc_num if its non-null otherwise you'd branch and re-create the record before returning the just queried maximum + 1. David J.
