Thursday, February 15, 2018

PostgreSQL–Generate an auto incrementing number on year basis

For an application we are building we had the following requirement:

A unique increasing number should be generated for every document on a year by year basis. So at the end of each year the document counter should be reset to 0 and start to increase again.

As we are using PostgreSQL as our database, I decided to implement this feature using sequences.

First thing I did was creating a function that generates a new sequence based on a specific name:

This allows me to create a new sequence dynamically at the beginning of each year.

Next thing I did was creating another function that will first invoke the f_create_seq function to see if a sequence already exists and then calls this sequence to get the next number in line.

I invoke this function from my application where I pass a year as the sequence name parameter: