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 ???
asked Jun 11, 2013 at 9:18
Abubacker Nainamohamed
4,8576 gold badges34 silver badges35 bronze badges
-
1It is better to deal with formatting issues at at the presentation layer. Keep it as numeric in the DB.Clodoaldo Neto– Clodoaldo Neto2013-06-11 11:33:43 +00:00Commented Jun 11, 2013 at 11:33
Add a comment
|
2 Answers
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
);
Comments
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;
