Padding zeros to the left in postgreSQL

Question

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.

Answer 1

You can use the rpad and lpad functions to pad numbers to the right or to the left, respectively. Note that this does not work directly on numbers, so you'll have to use ::char or ::text to cast them:

SELECT RPAD(numcol::text, 3, '0'), -- Zero-pads to the right up to the length of 3
       LPAD(numcol::text, 3, '0')  -- Zero-pads to the left up to the length of 3
FROM   my_table

Answer 2

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

Answer 3

As easy as

SELECT lpad(42::text, 4, '0')

References:

• http://www.postgresql.org/docs/current/static/functions-string.html

sqlfiddle: http://sqlfiddle.com/#!15/d41d8/3665

Answer 4

I was frustrated with LPAD and TO_CHAR since they don't work if your string goes above the minimum length. I can't speak for how efficient this is but you can chain FORMAT to give you a minimum sized string, and then replace any spaces with zeros using REPLACE

with sample_numbers (nr) as (
    values (1),(11),(100),(1000)
)
SELECT REPLACE(FORMAT('%3s', nr), ' ', '0')
from sample_numbers;
 replace
---------
 001
 011
 100
 1000
(4 rows)

Versus with other methods TO_CHAR:

with sample_numbers (nr) as (
    values (1),(11),(100),(1000)
)
SELECT to_char(nr, 'fm000')
from sample_numbers;
 to_char
---------
 001
 011
 100
 ###
(4 rows)

(Notice the last value is ### and not 1000)

And LPAD:

with sample_numbers (nr) as (
    values (1),(11),(100),(1000)
)
SELECT LPAD(nr::varchar(255), 3, '0')
from sample_numbers;
 lpad
------
 001
 011
 100
 100
(4 rows)

(Notice the last value is 100 and not 1000)

There are other methods involving using CASE but I like that the REPLACE & FORMAT combo doesn't need any variable repetition.

Answer 5

The easiest way:

ltrim(to_char(Column1, '000'))