Set a column value based on a condition

Question

I am using a table on a Postgres DB. I have a table with the following columns:

ID
STATE
LOB
CREATED TIMESTAMP
SUBMISSION NUMBER
SUBMISSION AMOUNT

This table is being updated by 2 different insert queries:

1. One where we receive the value of SUBMISSION NUMBER in the insert query
2. One where we do not receive the value of SUBMISSION NUMBER in the insert query. In this case the value of SUBMISSION NUMBER needs to be incremented by 1 based on the last available value of the column

This would not work if I have an auto incremented column of SUBMISSION NUMBER. If I do not have an auto incremented column, then would I create a sequence to do this? Or would I develop a function to do this? What's the best way one could achieve this?

Answer 1

You would need to implement a function that will modify NEW and call it in a BEFORE INSERT trigger. However, this most definitely will go badly if there are multiple inserts happen at the same time.

Answer 2

You can use auto-generated for the sequence_number column. You achieve by defining the auto generation as 'BY DEFAULT' rather than 'ALWAYS'. By doing so when the submission_number is not in the insert column list (or value set as default) the next value from the sequence is assigned. When the column is in the insert list and a value specified then that value is stored. Your table definition becomes something like: (see demo)

create table test( id                integer     generated always as identity
                 , state             text
                 , lob               text
                 , created           timestamptz default clock_timestamp()
                 , submission_number integer     not null 
                                                 generated by default as identity  
                 , submission_amount numeric     not null 
                 );