Zero Padding in PostgreSQL

Standard

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:

  1. Use the TO_CHAR function to cast whatever numeric you’ve got (could be a calculation, case statement, etc.) as a character string.
  2. 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.
  3. 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'))

2 thoughts on “Zero Padding in PostgreSQL

Leave a Reply

Your email address will not be published. Required fields are marked *