10

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?

Vasilen Donchev
  • 975
  • 4
  • 14
  • 26
  • 1
    `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. –  Oct 17 '16 at 10:22

2 Answers2

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.

m1tk4
  • 3,439
  • 1
  • 22
  • 27
  • 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 :) – Vasilen Donchev Jul 29 '11 at 06:27
  • 3
    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. – m1tk4 Jul 30 '11 at 03:36
  • 1
    You can remove the space from to_char() by using the `FM` prefix: `to_char(1, 'FM0000')` – cimmanon Oct 20 '12 at 22:58
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')
fthiella
  • 48,073
  • 15
  • 90
  • 106
Andrey
  • 307
  • 4
  • 5
  • 2
    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')`. – Peter Krauss May 23 '14 at 09:35