PostgreSQL to_number() Function

The PostgreSQL to_number() function converts the specified string to a number according to the given format.

to_number() Syntax

This is the syntax of the PostgreSQL to_number() function:

to_number(str TEXT, format TEXT) -> NUMERIC



Required. A string that will to be converted to a number.


Required. The format used for converting this string. You can use the template patterns in the following table to construct numeric format strings.

Pattern Description
9 digit position (can be discarded if it doesn’t matter)
0 digit position (will not be removed, even if it doesn’t matter)
. decimal point
, group (thousands) separator
PR negative values in angle brackets
S currency symbol (uses locale)
L Currency symbol (uses locale)
D decimal point (uses locale)
G group separator (uses locale)
MI minus sign in specified position (if number < 0)
PL plus sign in specified position (if number > 0)
SG plus/minus sign in specified position
RN Roman numerals (between 1 and 3999)
TH,th ordinal number suffix
V shift specified number of digits
EEEE exponent for scientific notation

Return value

The PostgreSQL to_number() function returns a numeric value converted from the specified string according to the specified format.

to_number() Examples

This example shows how to use the PostgreSQL to_number() function to convert a specified string to a numeric value according to the specified format.

SELECT to_number('123,456,789.012-', '999G999G999D999S');