CODEWITHSUNDEEP
postgresqlzerofill
Vasilen Donchev
Vasilen Donchev

Reputation: 1035

How to Create PostgreSQL Leading Zero Sequence (zerofill)

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?

Upvotes: 11

Views: 18927

Answers (2)

Andrey
Andrey

Reputation: 317

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')

Upvotes: 11

m1tk4
m1tk4

Reputation: 3467

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.

Upvotes: 14

Related Questions