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?

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. — user330315
– user330315, Commented Oct 17, 2016 at 10:22

m1tk4 · Accepted Answer · 2011-07-28 17:22:27Z

14

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.

answered Jul 28, 2011 at 17:22

m1tk4

3,4771 gold badge25 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Vasilen Donchev Over a year ago

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

m1tk4 Over a year ago

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.

cimmanon Over a year ago

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

fthiella · Accepted Answer · 2016-10-17 10:16:49Z

11

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

edited Oct 17, 2016 at 10:16

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

answered Jun 7, 2013 at 16:27

Andrey

3274 silver badges5 bronze badges

1 Comment

Peter Krauss Over a year ago

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

Collectives™ on Stack Overflow

How to Create PostgreSQL Leading Zero Sequence (zerofill)

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related