Generate an account number with sequence-CodePudding

I have a requirement where I need to generate an account number and insert it into a table column in the following format. "TBA2222011300000001" = where "TBA" is the value of another column or user sent data "22220113" implies the current date and "00000001" is a seven digit sequence that needs to be incremented and appended for every insert.

How can I append the sequence to the column, Should I do it in java or can it be done at DB end. I am currently using postgres with java and spring boot.

CodePudding user response：

https://www.postgresql.org/docs/current/ddl-generated-columns.html

A generated column is a special column that is always computed from other columns.
Several restrictions apply to the definition of generated columns and tables involving generated columns:

The generation expression can only use immutable functions and cannot use subqueries or reference anything other than the current row in any way.

now() is mutable function, so you cannot use Generated columns. I am not sure why Default not working.
https://www.postgresql.org/docs/current/ddl-default.html So now the only option is trigger.

CREATE TABLE account_info(
    account_id INT GENERATED ALWAYS AS IDENTITY,
    account_type text not null,
    acconut_number text ) ;

So what you want is to automate:

UPDATE account_info set 
    account_number = 
    concat(
        account_type,
        to_char(CURRENT_DATE, 'yyyymmdd'),
        to_char(account_id, 'FM00000000'));

create the function

create or replace function update_account_number() returns trigger as $$
BEGIN
UPDATE account_info set 
    account_number = 
    concat(
        account_type,
        to_char(CURRENT_DATE, 'yyyymmdd'),
        to_char(account_id, 'FM00000000'));
RETURN NULL; 
end;
$$  LANGUAGE plpgSQL;

create the trigger:

CREATE OR REPLACE TRIGGER udpate_accout_number
    AFTER INSERT ON account_info
    FOR EACH ROW
    EXECUTE FUNCTION update_account_number();

CodePudding user response：

Have an id column which is identity in postgres with start and end index as required. For your reference to create identity column as desired https://www.postgresqltutorial.com/postgresql-identity-column/

Have 1 more column for createdDate.

Then account number is simply a derived value TBA formatted(DATE) formatted(Id). Ex - No not a trigger just a function. There won't be any account number column in your table. It will simply be a function which takes date and identity as input and gives account number as output. Since account number is only dependent on id and date. No need to store this value at all, whenever you need the account number just call that function. Account number will not exist at all. It will always be calculated based on id and date. Simple.

Refer this in the article Method 1: Derived Value called "markup"

The first method we may want to add to this table is a accountNumber method, for calculating our accountNumber based on current date and id. Since this value will always be based on two other stored values, there is no sense in storing it (other than possibly in a pre-calculated index). To do this, we:

CREATE FUNCTION accountNumber(id,date) RETURNS varchar AS
$$ SELECT TBA   format(id)   format(date)
$$ LANGUAGE SQL IMMUTABLE;

You need to put logic for format(id) and format(date) as per your requirement.

There is no point of storing the value which can be easily derived from other 2 columns. It would unnecessary consume space. Also maintaining data integrity and checks will be an overhead.

Creating function for derived values https://ledgersmbdev.blogspot.com/2012/08/postgresql-or-modelling-part-2-intro-to.html

You can use the function in output as well as search. Index would also be utilized as required.