Assume I have a table, loan_main, which have a primary key, loan_id, with numbering rule : LN_YYYYMMDD_9999999999.
How to insert data into loan_main automatically without caring how to build loan_id with this numbering rule.
How-to
In this case, we need to know
1. how to concatenate string, i.e. ||
2. how to get current date with specific format:
select to_char(now(), 'YYYYMMDD')
3. how to get value from sequence and pad zero to the left:
select LPAD(nextval('loan_sequence')::text, 10, '0')
The syntax looks like:
ALTER TABLE loan_main ALTER COLUMN loan_id SET DEFAULT ('LN_' || (to_char(now(), 'YYYYMMDD')) || '_' || LPAD(nextval('loan_sequence')::text, 10, '0'));
No comments:
Post a Comment