How to Auto Increment a column in postgres? Like some fixed text after that incrementing number

Question

I am new to postgres and trying to create a schema. I Have a requirement in which i need to auto increment the Id column of my table. But i dont want it to be just a number but some fixed value followed by incrementing number. Example -

DAG100H001
DAG100H002
DAG100H003
DAG100H004

something like this. I was looking at the documentation and found serial but i am not sure how to implement using that. Any help will appreciated. Thanks.

Answer 1

Use a sequence and a prefix:

CREATE SEQUENCE seq INCREMENT BY 1;

How to use it:

SELECT 
  'DAG100H'||lpad(nextval('seq')::text,3,'0'),
  'DAG100H'||lpad(nextval('seq')::text,3,'0');

  ?column?  |  ?column?  
------------+------------
 DAG100H001 | DAG100H002
(1 row)

You might wanna add it directly to your table:

CREATE TABLE t (
  id text DEFAULT 'DAG100H'||lpad(nextval('seq')::text,3,'0'), 
txt text);

So that you can insert values ..

INSERT INTO t (txt) VALUES ('foo'),('bar');

.. and they get the id you want

SELECT * FROM t;

     id     | txt 
------------+-----
 DAG100H001 | foo
 DAG100H002 | bar
(2 rows)

• Mind the comments section of your question, as they make a very good point! Consider using a normal numeric sequence and just add the prefix to the client by concatenating it with the column: 'DAG100H'||lpad(nextval('seq')::text,3,'0'), ..