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 !!!