postgres : create a sequence like 0000001 to 00000n

Question

Dear All,
I want to create a sequence in postgres which is like 0000000001 to 00nnnnnnnnn normally we can create from 1 to n , but I want that to be preceeded with 0's
Is there any easy way to do this ???

Answer 1

sequence is number generator, a number doesn't have the '0' left padding...

if you want to add padding you can use the lpad function:

CREATE SEQUENCE my_sequence_seq;

SELECT lpad(nextval('my_sequence_seq')::text,10,'0');

you can use it also in the table declaration:

CREATE TABLE sequence_test(
  id varchar(20) NOT NULL DEFAULT lpad(nextval('my_sequence_seq')::text,10,'0'),
  name text
);

Answer 2

PostgreSQL sequences can only return INTEGER or BIGINT values, so normally numbers 1 and 0000001 are considered the same, 1 being canonical representation.

I am not sure why would you want to do this, but you can convert sequence number to string and prepend appropriate number of 0 characters, something like this:

SELECT repeat('0', 7 - length(nextval('myseq'))) || currval('myseq')::text

Where 7 is total number of digits you need (code may stop working if number is beyond that number of digits).

Note that you will need to create sequence myseq as source for your numbers:

CREATE SEQUENCE myseq;