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?
CodePudding user response:
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.
CodePudding user response:
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
);