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 9s, 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^n, 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 and 9 are equivalent when used with the to_number() function.
• When using the to_number() function, if non-data template patterns such as L or TH 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).