This page contains the full list of template patterns and template pattern modifiers that can be used when formatting numbers in PostgreSQL.
Template Patterns
The following table lists all template patterns that can be used for numeric formatting in Postgres.
Pattern | Description |
---|---|
9 | Digit position (can be dropped if insignificant). If the digit is a leading zero then it will be replaced by a space. If it’s a trailing zero and fill mode is specified (i.e. FM ) then it will be deleted. |
0 | Digit position (will not be dropped, even if insignificant). This means that the digit position will always be printed, even if it contains a leading/trailing zero. |
. (period) | Decimal point. Ignores locale. |
, (comma) | Group (thousands) separator. Ignores locale. |
PR | Negative value in angle brackets. |
S | Sign anchored to number (uses locale). If S appears just left of one or more 9 s, the sign will be anchored to the number. |
L | Currency symbol (uses locale). |
D | Decimal point (uses locale). |
G | Group separator (uses locale). |
MI | Minus sign in specified position (if number < 0). Not anchored to the number. |
PL | Plus sign in specified position (if number > 0). Not anchored to the number. This is a Postgres extension. |
SG | Plus/minus sign in specified position. Not anchored to the number. This is a Postgres extension. |
RN | Roman numeral (input between 1 and 3999). |
TH or th | Ordinal number suffix. These do not convert values less than zero and they do not convert fractional numbers. These are PostgreSQL extensions. |
V | Shift specified number of digits. When used with to_char() , this multiplies the input values by 10^ , where n is the number of digits following V . When used with to_number() , it divides in a similar manner. Both to_char() and to_number() do not support the use of V combined with a decimal point (e.g., 99.9V99 is not allowed). |
EEEE | Exponent for scientific notation. Cannot be used in combination with any of the other formatting patterns or modifiers other than digit and decimal point patterns, and must be at the end of the format string (e.g., 9.99EEEE is a valid pattern). |
Notes:
- Both
0
and9
are equivalent when used with theto_number()
function. - When using the
to_number()
function, if non-data template patterns such asL
orTH
are used, the corresponding number of input characters are skipped, whether or not they match the template pattern, unless they are data characters (i.e., digits, sign, decimal point, or comma). For example,TH
would skip two non-data characters.
Template Pattern Modifiers
The following modifiers can be applied to the above template patterns to alter the result.
Modifier | Description |
---|---|
FM prefix | Fill mode (suppress trailing zeroes and padding blanks). |
TH suffix | Upper case ordinal number suffix. Does not convert values less than zero and does not convert fractional numbers. This is a Postgres extension. |
th suffix | Lower case ordinal number suffix. Does not convert values less than zero and does not convert fractional numbers. This is a Postgres extension. |
Note that the first is added as a prefix to the template pattern and other two are added as a suffix.
Example
Here’s a quick example that includes both a template pattern and a modifier.
SELECT to_char(1, '9th');
Result:
1st
In this case, the 9
was for the number (1), and the th
was used to add the ordinal indicator (in this case, st).