How to create auto incremented alphanumeric id in postgres


A few days ago, i got a condition where i need to create an alphanumeric auto incremented id in Postgres. I have created many auto incremented numeric ids before, but never got a use case like this. This was new for me.  Therefore, here i am going to share the solution for the same which can be useful for you as well.

First , we will create a sequence :


CREATE SEQUENCE employee_id_seq;

Then, if you want to start your id from a particular number, then do as follows :


SELECT setval('employee_id_seq',100);

Here, id will start from 101. If you will not use above line then it will start from 1.

Now define the column which you want to be alphanumeric auto incremented :


id text DEFAULT 'RK' || nextval('employee_id_seq')

Here, id is the column name, while “RK” is the string which will be prefixed to auto incremented number.

Full code :


CREATE SEQUENCE employee_id_seq;
SELECT setval('employee_id_seq',100);
CREATE TABLE employee(
id text DEFAULT 'RK' || nextval('employee_id_seq'),
name varchar(255) NOT NULL
);

Now, run below command and see what happens :


INSERT INTO employee values(2,'Rishi');

A row will be inserted into employee table containing 2 as a id.

If you dont want to insert ids which does not have “RK” as prefix, then use the below line :


id text CHECK (id ~ '^RK[0-9]+$') DEFAULT 'RK' || nextval('employee_id_seq')

Full code for id condition check :


CREATE SEQUENCE employee_id_seq;
SELECT setval('employee_id_seq',100);
CREATE TABLE employee(
id text CHECK (id ~ '^RK[0-9]+$') DEFAULT 'RK' || nextval('employee_id_seq'),
name varchar(255) NOT NULL
);

That’s all. Hope, it will help you guyz.

Cheers !!!

 

 

About Rishi Khandelwal

Sr. Software Engineer having more than 5 years industry experience. He has working experience in various technologies such as Scala, Java, Play, Akka, Lift Web, Spark, ElasticSearch, Backbone.js, html5, javascript, Less, Amazon EC2, WebRTC, SBT
This entry was posted in Scala. Bookmark the permalink.

One Response to How to create auto incremented alphanumeric id in postgres

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s