CODEWITHSUNDEEP
sqlpostgresql
Joey Yi Zhao
Joey Yi Zhao

Reputation: 42426

How can I use auto incremental column to generate a fixed length string in postgresql?

I am using Postgresql 11 and I have a string column which is fixed 8 length digital number. The value should be in sequence 00000001, 00000002 etc. I know Postgresql supports SEQUENCE column but how can I make it to be a fixed length string?

One solution is to use two SQL, one is to insert the row with an SEQUENCE column (which is integer), the other sql is to update the row based on the SEQUENCE column value to make it to be e.g. 00000001.

I also come cross LPAD function but not sure whether it works for this case.

Upvotes: 1

Views: 933

Answers (2)

user330315
user330315

Reputation:

I wouldn't store the number that way to begin with. Create an identity column and use to_char() or lpad() when displaying the value. If you need this a lot, you can create a view to avoid writing the expression all the time:

create table the_table
(
  id integer generated always as identity (maxvalue 99999999),
  ... other columns
);


create view the_table_with_formatted_id
as
select lpad(id::text, 8, '0') as formatted_id, 
       ... other columns
from the_table;

Another option is to use the sequence in a default value for a text column:

create sequence the_table_id_seq;
create table t
( 
  id text default lpad(nextval('the_table_id_seq')::text, 8, '0'), 
  ... other columns ...
);

Unlike the identity column, the above does not prevent INSERTs bypassing the sequence and storing e.g. abcdefgh into that column. If you want to ensure it's a strictly numeric (but formatted) value, using an identity columns is a much better choice.

Upvotes: 2

Jim Jones
Jim Jones

Reputation: 19613

Just create a standard sequence and then use lpad to pad the zeros you want:

CREATE SEQUENCE seq1;

SELECT lpad(nextval('seq1')::text,8,'0');

   lpad   
----------
 00000001

EDIT: as mentioned in the comments by @a_horse_with_no_name, to_char() is a neat alternative

SELECT to_char(nextval('seq1'), 'FM00000000');
 to_char  
----------
 00000001

Demo: db<>fiddle

Upvotes: 1

Related Questions