When working with Postgres, you can use the to_char() function to output numbers in a given format.
The way it works is that you provide two arguments. The first argument is the number to be formatted. The second argument determines how it is formatted.
The return value is text.
Depending on your requirements, you might prefer to convert the number to the appropriate data type, for example using the cast() function.
Examples of both methods are below.
Example
Here’s a basic example to demonstrate.
SELECT to_char(10, '99.99');Result:
10.00
The 9s actually have special meaning in this context. This character is one of the valid template patterns that your format string can consist of when formatting numbers.
In the case of 9, it specifies a digit position, but if it is a leading zero then it will be replaced by a space, while if it is a trailing zero and fill mode is specified then it will be deleted.
If I remove the decimal place and fractional seconds portion, I get a different result.
SELECT to_char(10, '99');Result:
10
Drop Leading Zeros
You can replace 9 with 0 if you want to suppress leading and trailing zeros, even when insignificant.
Here’s an example that demonstrates the difference between the two.
SELECT
to_char(0010, '9999') AS "9999",
to_char(0010, '0000') AS "0000";Result:
9999 | 0000 -------+------- 10 | 0010
Template Patterns
As seen in the previous examples, the format string consists of one or more template patterns.
For example, 9999 is a format string that consists of four template patterns – each of which are 9.
The decimal place is another template pattern.
I could have used a D instead of the decimal place. That uses the current locale for the actual formatting.
SELECT
to_char(10, '99.99') AS "99.99",
to_char(10, '99D99') AS "99D99";Result:
99.99 | 99D99 --------+-------- 10.00 | 10.00
For a full list of template patterns and modifiers, see Template Patterns & Modifiers for Numeric Formatting in Postgres.
Template Pattern Modifiers
There are also a few modifiers that can be used along with the template pattern. These change the way the output is formatted, depending on the modifier used.
Specifically, there are three modifiers that can be used with numeric formatting.
| Modifier | Description |
|---|---|
FM prefix | fill mode (suppress trailing zeroes and padding blanks) |
TH suffix | upper case ordinal number suffix |
th suffix | lower case ordinal number suffix |
Note that the first is added as a prefix to the template pattern and other two are added as a suffix.
Ordinal Numbers
A common requirement is to convert a number into its ordinal. We can use either TH or th for that, depending on whether we want the suffix to be uppercase or lowercase.
Here’s an example.
SELECT to_char(03, 'FM99th');Result:
3rd
In this case I also used FM to suppress any blanks and trailing zeros.
Here’s a comparison between using FM and omitting it:
SELECT
to_char(03, '99th') AS "99th",
to_char(03, 'FM99th') AS "FM99th";Result:
99th | FM99th -------+-------- 3rd | 3rd
If you look closely, the first column has leading blanks, whereas the second column doesn’t.
Currency
Another common requirement is to output a number in a given currency.
In this case, we can use the L template pattern to display a locale-aware currency symbol.
SELECT to_char(03, 'LFM99D00');Result:
$3.00
Signed Numbers
There are also template patterns for signed numbers.
Here’s an example.
SELECT to_char(27, 'S99');Result:
+27
Here, I used S to add a locale-aware sign that’s anchored to the number.
In this case it was a positive number. Here’s another example that uses a negative value.
SELECT to_char(-27, 'S99');Result:
-27
There are several other template patterns that can be used for signed numbers.
For examples, see Prepend a Plus/Minus Sign to a Number in Postgres.
And as mentioned, check out Template Patterns & Modifiers for Numeric Formatting in Postgres for a full list of template modifiers that you can use when formatting numbers.
Data Type
As mentioned, the to_char() function outputs its result as text.
Depending on your requirements, you might find it better to convert the number to a more appropriate data type.
Here’s an example that converts a number to the money data type.
SELECT cast(10 as money);Result:
$10.00