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 Consultant having more than 6 years industry experience. He has working experience in various technologies such as Scala, Java, Play, Akka, Spark, Hive, Cassandra, Akka-http, 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