From: | rob stone <floriparob(at)gmail(dot)com> |
---|---|
To: | marcelo <marcelo(dot)nicolet(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: sequence used on null value or get the max value for a column whith concurrency |
Date: | 2017-08-11 14:05:40 |
Message-ID: | 1502460340.5731.1.camel@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Murtuza Zabuawala | 2017-08-11 14:15:29 | Fwd: 2 process postgres -D for one instance |
Previous Message | Fabiana Zioti | 2017-08-11 13:09:13 | Receive a string in Composite-type Arguments |