In PostgreSQL, we can use the TO_CHAR() function to add leading zeros to a number. The function converts the number to a string, using the (optional) format we specify.

Another option is to use the LPAD() function to pad a number with leading zeros.

The TO_CHAR() Function

When using the TO_CHAR() function, use the 0 format element to include leading and/or trailing zeros.

Example:

SELECT TO_CHAR(7, 'fm000');

Result:

007

Here it is when compared to the 9 format element:

SELECT 
    TO_CHAR(7, 'fm999') AS "9",
    TO_CHAR(7, 'fm000') AS "0";

Result:

 9 |  0  
---+-----
 7 | 007

The number of leading zeros are determined by the number of 0 format elements and the number of digits in the number:

SELECT 
    TO_CHAR(77, 'fm0') AS "r1",
    TO_CHAR(77, 'fm00000') AS "r2",
    TO_CHAR(777, 'fm00000') AS "r3",
    TO_CHAR(7777, 'fm00000') AS "r4",
    TO_CHAR(77777, 'fm00000') AS "r5";

Result:

 r1 |  r2   |  r3   |  r4   |  r5   
----+-------+-------+-------+-------
 #  | 00077 | 00777 | 07777 | 77777

The fm format modifier suppresses any leading and/or trailing padding that might be included in the result. For example, when the number is negative, a minus sign is prefixed. But when the number is positive, no sign is included, and a space appears where the plus sign would have been.

Here’s an example to demonstrate this:

SELECT 
    TO_CHAR(-7, '000') AS "Minus Sign",
    TO_CHAR(7, '000') AS "Padded",
    TO_CHAR(7, 'fm000') AS "Not Padded";

Result:

 Minus Sign | Padded | Not Padded 
------------+--------+------------
 -007       |  007   | 007

The LPAD() Function

We can alternatively use the LPAD() function to pad numbers with leading zeros:

SELECT LPAD('7', 3, '0');

Result:

007

Another example:

SELECT 
    LPAD('77', 1, '0') AS "r1",
    LPAD('77', 5, '0') AS "r2",
    LPAD('777', 5, '0') AS "r3",
    LPAD('7777', 5, '0') AS "r4",
    LPAD('77777', 5, '0') AS "r5";

Result:

 r1 |  r2   |  r3   |  r4   |  r5   
----+-------+-------+-------+-------
 7  | 00077 | 00777 | 07777 | 77777

Note that this function requires that the number is passed as a string. Therefore, we need to convert the number to a string first. The padding character also needs to be a string.

So we may have to do something like this:

SELECT LPAD(CAST(7 AS varchar), 3, '0');

Result:

007

One of the benefits that LPAD() has over the previous method is that we can prefix the result other characters – it doesn’t need to be a leading zero.

SELECT 
    LPAD('77', 1, '.') AS "r1",
    LPAD('77', 5, '.') AS "r2",
    LPAD('777', 5, '.') AS "r3",
    LPAD('7777', 5, '.') AS "r4",
    LPAD('77777', 5, '.') AS "r5";

Result:

 r1 |  r2   |  r3   |  r4   |  r5   
----+-------+-------+-------+-------
 7  | ...77 | ..777 | .7777 | 77777