How to Auto Increment Alpha-Numeric value in postgresql?

Question

I am using "PostgreSQL 9.3.5"

I have a Table(StackOverflowTable) with columns (SoId,SoName,SoDob).

I want a Sequence generator for column SoId which is a Alpha-numeric value.

I want to auto increment a Alpha-Numeric Value in postgresql.

For eg : SO10001, SO10002, SO10003.....SO99999.

Edit:

If tomorrow i need to generate a Sequence which can be as SO1000E100, SO1000E101,... and which has a good performance. Then what is the best solution!

Answer 1

Use sequences and default value for id:

postgres=# CREATE SEQUENCE xxx;
CREATE SEQUENCE
postgres=# SELECT setval('xxx', 10000);
 setval 
--------
  10000
(1 row)

postgres=# CREATE TABLE foo(id text PRIMARY KEY 
                                    CHECK (id ~ '^SO[0-9]+$' ) 
                                    DEFAULT 'SO'  || nextval('xxx'), 
                            b integer);
CREATE TABLE
postgres=# insert into foo(b) values(10);
INSERT 0 1
postgres=# insert into foo(b) values(20); 
INSERT 0 1
postgres=# SELECT * FROM foo;
   id    | b  
---------+----
 SO10001 | 10
 SO10002 | 20
(2 rows)