How to Create PostgreSQL Leading Zero Sequence (zerofill)

Question

How can I create a leading zero sequence in PostgreSQL?

For MySQL I know it is BIGINT(10) UNSIGNED ZEROFILL AUTO_INCREMENT but in PostgreSQL I can't find an equivalent (only bigserial).

Moreover how can I limit the number of zeroes as BIGINT(10) means 10 symbols, does the type bigserial have such a limit?

Answer 1

Create a regular sequence, then use to_char() to pad it with leading zeroes. Your data type will be char(), though, Postgres does not support zerofill for integral types.

Answer 2

A good alternative to to_char() in a "fill leading zeros" task is lpad():

create table TableName(columnName serial primary key);

select lpad(columnName::text, 3, '0'), * from TableName;

Caution: lpad() does not generate an error on overflow, see for example:

select lpad(4444::text, 3, '0'), to_char(4444, '000')