Fun new conundrum in dealing with data formats/displays in PostgreSQL! A client recently requested that we provide a data extract that included percentages as a three character number, left zero-padded, rather than a decimal/numeric. There’s not a great way in Postgres to show a numeric with leading zeros (actually, I’ve yet to find a data type that does this consistently as a built-in to any platform). Instead, you have to do a little bit of work to get to your end result as a character (or text) field.
Using the TO_CHAR function and numeric patterns, you can get to what you’re looking for.
trim(to_char(numeric_value, '000'))
Here’s the step by step:
- Use the TO_CHAR function to cast whatever numeric you’ve got (could be a calculation, case statement, etc.) as a character string.
- The second parameter of your TO_CHAR function is the pattern you wish to match. For the code above, this will generate a 3-character string with zeros in front.
- Use TRIM to remove excess whitespace. For some reason, the TO_CHAR function adds a leading space.
Caveats
- There are two versions of this pattern that could work. The first uses 000, which is basically indicating three characters with leading zeros and ignoring how many numerals would be in the value to pad. Theoretically, you could also use the following to do the same thing. While it specifies to show one numeral, it will return multiple for values larger than one digit.
trim(to_char(numeric_value, '009'))
lpad(numcol::text, 3, ‘0’) adds also leading zeros up to the length of 3
Indeed it does. Thank you for the alternative!
how can i remove the leading zero’s in time when they appear like 09:30? but not remove anything when its like 12:30? im using postgres 9.5 . thanks!
This depends a bit on how the data is stored. If it is stored as a datetime (timestamp) then it’s really just a display issue in your UI. If it is stores as character, you have more ability to format as needed but less to use it in calculations and such.