Creating Auto Number field with preceding number of 0's? Scenario: I have a table with a (int4) field 'pg_recordid' that has newly inserted records auto-increment the new record id using this macro after running 'SERIAL' in the field (in Navicat)

nextval('raw_casper_pg_recordid_seq'::regclass)

The current output for the first record: 1

Is there a method to set the macro so the Auto numbers have the entire number length filled with 0's for unused digits? example: 000001
Without getting into a technical argument, once you start getting fussy about the display of a number, it is no longer just a number. (Mathematically, we refer to a number as its value, while its display is referred to a numeral).

Why is this important? What you are asking for is not actually a number, but some way of displaying it. The short answer is that you will need to convert it to a string.

If you want to zero-pad a number, you use a combination of string operations:


::char will convert a value to a string
lpad(string,length,padding) will left-pad a string with the given padding.


Combining the two you get:

select lpad(id::char,4,'0'), …
from …;


where id is presumed to be your auto-numbered column.