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:
- One where we receive the value of SUBMISSION NUMBER in the insert query
- 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?
2
Answers
You would need to implement a function that will modify
NEW
and call it in aBEFORE INSERT
trigger. However, this most definitely will go badly if there are multiple inserts happen at the same time.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 thesubmission_number
is not in the insert column list (or value set asdefault
) 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)