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?

Hylan

BIGINT(10) does not limit the values to only 10 digits in MySQL. You can store any number you like in a column defined like that. – Teammate

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.

Chieftain

Yeah, I thought of something like this, but I was wondering if there is another way. So it comes something like this: CREATE SEQUENCE seq_test INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1; and then for the key: id character(11) NOT NULL DEFAULT to_char(nextval('seq_test'), '0000000000') PRIMARY KEY. Still wondering why character(10) doesn't work while the symbols are 10 :) – Hylan

to_char() adds an extra space before the digits that is used for negative sign when your integer is negative. I.e. if you use 10 '0's in the pattern you'll get an 11-character string. – Chieftain

You can remove the space from to_char() by using the FM prefix: to_char(1, 'FM0000') – Virgulate

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

Commonage

Translating to a generic answer: lpad() is a good alternative to to_char() in a "fill leading zeros" task. CAUTION: lpad not generates an error on overflow, see SELECT lpad(4444::text, 3, '0'), to_char(4444, '000'). – Cringe