Thinking aloud...
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.
