SQL Pocket Guide, 3rd Edition

Numeric Conversions: PostgreSQL

You can convert between numeric values and their string representations using the following functions, where number can be any numeric type:

TO_CHAR(number, format)
TO_NUMBER(string, format)

PostgreSQL’s number format elements closely follow Oracle’s. They are listed in Table 1-10.

Table 1-10. PostgreSQL’s numeric format elements

Element	Description
$	Prefix: dollar sign ($).
, (comma)	Location of comma. Consider G instead.
. (period)	Location of period. Consider D instead.
0	Significant digit. Leading zeros.
9	Significant digit. Leading blanks.
B	Prefix: returns zero as blanks.
C	Location of ISO currency symbol.
D	Location of decimal point.
EEEE	Suffix: use scientific notation.
FM	Prefix: removes leading/trailing blanks.
G	Location of group separator.
L	Location of local currency symbol.
MI	Suffix: trailing minus (–) sign.
PR	Suffix: angle brackets (< and >) around negative values.
RN or rn	Roman numerals, upper- or lowercase. Output-only.
S	Prefix: leading plus (+) or minus (–) sign.
TM, TM9, TME	Prefix: use minimum number of characters (text-minimum). Output-only. TM9 gives decimal notation. TME gives scientific notation.
U	Specifies location of Euro symbol (€).
V	Multiplies the number to the left of the V in the format model by 10 raised to the nth power, where n is the number of 9s found after the V in the format model. See the example in Numeric Conversions: Oracle. Output-only.
X	Use hexadecimal notation. Output-only. Precede with 0s for leading zeros. Precede with FM to trim leading/trailing ...