PostgreSQL TO_NUMBER() Function By Examples

Summary: in this tutorial, you will learn how to use the PostgreSQL TO_NUMBER() function to convert a character string to a numeric value according to a specified format.

Introduction to the PostgreSQL TO_NUMBER() function

The PostgreSQL TO_NUMBER() function allows you to convert a string to a number based on a specified format.

Here’s the basic syntax of the TO_NUMBER() function:

TO_NUMBER(string, format)

The TO_NUMBER() function requires two arguments:

string: This is a string that you want to convert to a number.
format: This is the format that specifies how the string should be interpreted as a number.

The TO_NUMBER() function returns a value whose data type is numeric.

The following table illustrates the list of valid formats:

Format	Description
9	Numeric value with the specified number of digits
0	Numeric value with leading zeros
. (period)	decimal point
D	Sign anchored to a number that uses the locale
, (comma)	group (thousand) separator
FM	Fill mode, which suppresses padding blanks and leading zeroes.
PR	Negative value in angle brackets.
S	Sign anchored to a number that uses locale
L	Currency symbol that uses locale
G	Group separator that uses locale
MI	Minus sign in the specified position for numbers that are less than 0.
PL	Plus sign in the specified position for numbers that are greater than 0.
SG	Plus / minus sign in the specified position
RN	Roman numeral ranges from 1 to 3999 – currently, it does not work for the Roman numeric string.
TH or th	Upper case or lower case ordinal number suffix

Noted that these format strings are also applicable to TO_CHAR() function.

PostgreSQL TO_NUMBER() function examples

Let’s take a look at some examples of using the TO_NUMBER() function to understand how it works.

1) Converting a string to a number

The following example uses the TO_NUMBER() function to convert the string '12,345.6-' to a number.

SELECT
    TO_NUMBER(
        '12,345.6-',
        '99G999D9S'
    );

The output is:

to_number
-----------
  -12345.6
(1 row)

In this example:

'12,345.6-' is the input that we want to convert to a number. The input string consists of a group separator (,), a decimal point (.), and a minus sign (-) indicating a negative number.
'99G999D9S' is the format pattern used to interpret the input string. Each character in the format pattern has a specific meaning:
- 9: A digit placeholder.
- G: The group separator (,).
- D: The decimal point (.).
- S: The sign (either + or -).

The TO_NUMBER() parses the input string '12,345.6-' according to the format '99G999D9S' and returns a numeric value -12345.6

2) Converting a money amount to a number

The following example uses the TO_NUMBER() function to convert a money amount to a number:

SELECT
    TO_NUMBER(
        '$1,234,567.89',
        'L9G999g999.99'
    );

Here is the result:

to_number
-----------
 1234567.89
(1 row)

In this example:

'$1,234,567.89' is the input string representing a money amount. It includes a dollar sign ($), a group separator (,), a decimal point (.), and numeric digits.
'L9G999g999.99' is the format string that the TO_NUMBER() function interprets the money amount. Each character in the format string has a specific meaning:
- L: A local currency symbol (in this case, the dollar sign $).
- 9: A digit placeholder.
- G: The group separator (, in this case).
- g: An optional occurrence of the group separator (,), which allows for flexible formatting.
- .: The decimal point.
- 99: Two-digit placeholders for the fractional part (cents).

Since the provided format matches the input string, the function parses the string accordingly and returns the number 1234567.89.

3) Format control

If you don’t specify .99 in the format string, the TO_NUMBER() function will not parse the part after the decimal place. For example:

SELECT
    TO_NUMBER(
        '1,234,567.89',
        '9G999g999'
    );

It returned 1234567 instead of 1234567.89 as follows:

to_number
-----------
   1234567
(1 row)

4) Format string does not match the input string

The following statement uses the TO_NUMBER() function to convert a string to a number but the format string does not match:

SELECT TO_NUMBER('1,234,567.89', '999G999.99');

Output:

ERROR:  numeric field overflow
DETAIL:  A field with precision 6, scale 0 must round to an absolute value less than 10^6.

The TO_NUMBER() function issues an error in this case.

Summary

Use the PostgreSQL TO_NUMBER() function to convert a string to a numeric value.

PostgreSQL TO_NUMBER() Function