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)


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

  • 3
    You could use a view with an added column, computed from the serial and padded with 0s. Oct 22, 2016 at 8:34
  • 5
    Don't store the zeros, just add them when displaying them (e.g. by using to_char() to format the number.
    – user1822
    Oct 22, 2016 at 9:38
  • 1
    Are you trying to solve this in Navicat or in the database? I don't know about Navicat but PostgreSQL certainly doesn't have "macros".
    – Andriy M
    Oct 22, 2016 at 12:46
  • @a_horse_with_no_name thanks! Is this a function I can call on the same field, or do I have to create a new postgres field and call the function on the original field?
    – GPP
    Oct 28, 2016 at 0:04

1 Answer 1


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.

  • 2
    Solid answer. Stick around. Feb 20, 2017 at 6:26
  • Thanks! I came to a similar conclusion and decided that I liked the benefits of the record id as an integer just fine
    – GPP
    Feb 20, 2017 at 7:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.