Total Pageviews

2018/02/08

[PostgreSQL] How to assign customized numbering rule in primary key

Problem
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: