How to auto increment alpha value in postgresql?

I am using "PostgreSQL 9.3.5"

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

I want a Sequence generator for a SoId column, which is an alpha-numeric value.

I want auto increment alpha value in postgresql.

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

Edit:

If tomorrow I need to generate a sequence that can be like SO1000E100, SO1000E101,... and which has good performance. Then the best solution!

+6
source share
3 answers

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) 
+3
source

You can define the default value for your column as the concatenation of S and the regular sequence , as shown below:

 CREATE SEQUENCE sequence_for_alpha_numeric INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1; CREATE TABLE table1 ( alpha_num_auto_increment_col character varying NOT NULL, sample_data_col character varying, CONSTRAINT table1_pkey PRIMARY KEY (alpha_num_auto_increment_col) ) ; ALTER TABLE table1 ALTER COLUMN alpha_num_auto_increment_col SET DEFAULT TO_CHAR(nextval('sequence_for_alpha_numeric'::regclass),'"S"fm000000'); 

Test:

  ^ insert into table1 (sample_data_col) values ('test1'); insert into table1 (sample_data_col) values ('test2'); insert into table1 (sample_data_col) values ('test3'); select * from table1; alpha_num_auto_increment_col | sample_data_col ------------------------------+----------------- S000001 | test1 S000002 | test2 S000003 | test3 (3 lignes) 

How to use sequences

How to use the to_char function.

+3
source

Create a sequence as shown below

  CREATE SEQUENCE seq_autoid INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 10000 

Create a function to generate an alphanumeric identifier

 create or replace function auto_id () returns varchar as $$ select 'SO'||nextval('seq_autoid') $$ language sql 

and try this example table

 create table AAA(id text ,namez text) insert into AAA values (auto_id(),'MyName') insert into AAA values (auto_id(),'MyName1') insert into AAA values (auto_id(),'MyName2') 
0
source

Source: https://habr.com/ru/post/978662/


All Articles