Padding zeros to the left in postgreSQL

sql postgresql zero-padding
Ben picture Ben · Oct 15, 2014 · Viewed 102.6k times · Source

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

Mureinik picture Mureinik · Oct 15, 2014

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

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How do I (or can I) SELECT DISTINCT on multiple columns?

I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales …

Read More
How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More