Create Postgresql Default Auto Increment Sequence number based in ID concat with Current Date format

Question

I want to get auto increment default sequence as CURRENT_TIMESTAMP (with 2 digits 'year' format) + ID (5 digits)...
Like this:
If current year is 2020,
2010001, 2010002, 2010003, ...

If current year is 2021,
2110001, 2110002, 2110003, ...

Please, help me out, and give me any idea.

Laurenz Albe · Accepted Answer · 2020-01-13 05:29:24Z

1

Use a normal sequence, set its value to 2010000, and at the beginning of 2021 set it to 2110000.

answered Jan 13, 2020 at 5:29

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Felix H. Over a year ago

I tested and it worked like this ... SELECT setval('seq_name', (CAST(TO_CHAR(now(), 'YY') || 10001 AS INTEGER)), FALSE)

Felix H. Over a year ago

I also thought another way ... Can I create that auto date concat sequence by using trigger for year by year?

Laurenz Albe Over a year ago

That would be slower, and you still would have to reset the sequence.

Collectives™ on Stack Overflow

Create Postgresql Default Auto Increment Sequence number based in ID concat with Current Date format

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related