157

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.

Braiam
  • 1
  • 11
  • 47
  • 78
Ben
  • 2,518
  • 4
  • 18
  • 31
  • 2
    Using google is *exactly* how I found this page. It was my number-one google result. – Jason Apr 04 '17 at 22:19
  • Possible duplicate of [to\_char(number) function in postgres](https://stackoverflow.com/questions/14155656/to-charnumber-function-in-postgres) – Evan Carroll Jun 08 '17 at 19:54
  • Speaking of SQL Server, they have the `format()` function, which will let you use `format(Column1,'000') as Column2`. – Manngo Oct 23 '18 at 09:42

4 Answers4

275

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
joelittlejohn
  • 11,665
  • 2
  • 41
  • 54
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • 1
    @Yarin [this answer](https://stackoverflow.com/a/26379893/124486) is the standardized one function call method with format strings. Using RPAD/LPAD you're converting to strings and then processing the strings. Using to_char, you're just specifying a different method for stringifying. – Evan Carroll Aug 19 '17 at 18:23
  • 3
    @EvanCarroll they're both useful - this one lets you specify a number for the string length - the other requires knowing 'fm' stands for fill mode and lets you specify a format picture – Brian Burns Mar 05 '18 at 10:52
  • 2
    WARNING: Unlike the classic `printf`, these bone-headed functions will silently chop your string down to size if it doesn't fit. So you might need a `case when length(foo) ...` around it. – Sam Watkins May 24 '19 at 01:11
79

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

Brian Burns
  • 20,575
  • 8
  • 83
  • 77
  • 4
    If the number is too long, `to_char` converts it to ###. O.o – Sam Watkins May 24 '19 at 01:13
  • 3
    I'm curious if there is a solution to the issue where if # if longer than specified in to_Char, it converts to ###. Is there anyway to specificy minimum # of zeros and then have larger numbers grow from that ? For example, if you specify 3 for lpad , numbers would be formatted like 001 010 100 .. 1001 – mike hennessy Apr 27 '20 at 17:09
  • @mikehennessy you can use 9's instead, so like `SELECT to_char((10 ^ abc), 'FM9999999000') FROM generate_series(0, 9) abc` – joyleak Apr 13 '22 at 14:50
21

As easy as

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

References:

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

Ihor Romanchenko
  • 26,995
  • 8
  • 48
  • 44
zerkms
  • 249,484
  • 69
  • 436
  • 539
2

The easiest way:

ltrim(to_char(Column1, '000'))
Vladimir Pankov
  • 377
  • 3
  • 8