[sql] Padding zeros to the left in postgreSQL

I am relatively new to PostgreSQL and I know how to pad a number with zeros to the left in SQL Server but I'm struggling to figure this out in PostgreSQL.

I have a number column where the maximum number of digits is 3 and the min is 1: if it's one digit it has two zeros to the left, and if it's 2 digits it has 1, e.g. 001, 058, 123.

In SQL Server I can use the following:

RIGHT('000' + cast([Column1] as varchar(3)), 3) as [Column2]

This does not exist in PostgreSQL. Any help would be appreciated.

This question is related to sql postgresql zero-padding

The answer is


The to_char() function is there to format numbers:

select to_char(column_1, 'fm000') as column_2
from some_table;

The fm prefix ("fill mode") avoids leading spaces in the resulting varchar. The 000 simply defines the number of digits you want to have.

psql (9.3.5)
Type "help" for help.

postgres=> with sample_numbers (nr) as (
postgres(>     values (1),(11),(100)
postgres(> )
postgres-> select to_char(nr, 'fm000')
postgres-> from sample_numbers;
 to_char
---------
 001
 011
 100
(3 rows)

postgres=>

For more details on the format picture, please see the manual:
http://www.postgresql.org/docs/current/static/functions-formatting.html