In PostgreSQL, you can use the to_number() function to convert a string to a numeric value.

More specifically, it converts the string representation of a number to a numeric value.

For example, if you have $1,234.50 as a string, you can use to_number() to convert that to an actual number that uses the numeric data type.

Syntax

The syntax goes like this:

to_number(text, text)

Where the first argument is a string representation of the number, and the second argument defines the template that the first argument uses.

Example

Here’s a basic example to demonstrate.

SELECT to_number('80', '99');

Result:

80

In this case, I used 99 as the template. Each 9 is referred to as a “template pattern”. The 9 template pattern represents a digit position. I used two, because I wanted both digits to be included.

Here’s what happens if I remove one of the 9s.

SELECT to_number('80', '9');

Result:

8

So it’s important to include the correct number of template patterns in the template.

Group Separator and Decimal Point

When working with larger numbers and/or numbers with fractional seconds, you’ll need to include template patterns that specify the group separator and/or the decimal point.

There are two ways you can do this.

The first option is to literally type out the comma and decimal point.

SELECT to_number('7,000.25', '9,999.99');

Result:

7000.25

The second option is to use the locale-aware versions. These are G for the group separator (thousands separator), and D for the decimal point.

So the previous example could be rewritten as follows:

SELECT to_number('7,000.25', '9G999D99');

Result:

7000.25

Currency Symbol

The L template pattern represents a locale aware currency symbol.

SELECT to_number('$7,000.25', 'L9G999D99');

Result:

7000.25

Return Type

The return value of the to_number() function is numeric.

You can check the return type with the pg_typeof() function.

SELECT pg_typeof(to_number('$7,000.25', 'L9G999D99'));

Result:

numeric

Unexpected Results?

It’s important to get the template right. Otherwise you could end up with unexpected results.

Here’s an example of what happens if I simply forget to include the L template pattern from the earlier example.

SELECT to_number('$7,000.25', '9G999D99');

Result:

7000

So because I forgot to include the L template pattern (for the currency), this put the whole template out of sync with the number, which resulted in the G being ignored, as well as the D.

Just to be clear, here it is again when compared to the correct template.

SELECT 
  to_number('$7,000.25', 'L9G999D99') AS "Right",
  to_number('$7,000.25', '9G999D99') AS "Wrong";

Result:

   Right | Wrong
---------+-------
 7000.25 | 7000

to_number() vs cast()

The to_number() function is provided mainly to handle input formats that cannot be converted by simple casting. It is therefore generally unnecessary for standard numeric representations.

So the first example on this page could have been done using cast().

SELECT cast('80' AS NUMERIC);

Result:

80

But we start to run into trouble once things get a bit more complex.

SELECT cast('$7,000.25' AS NUMERIC);

Result:

ERROR: invalid input syntax for type numeric: "$7,000.25"
LINE 1: SELECT cast('$7,000.25' AS NUMERIC);

So to_number() was mainly designed for situations such as this one.

Full List of Template Patterns & Modifiers

Postgres includes plenty more template patterns and modifiers.

These can also be used when formatting numeric values (for example when using the to_char() function to return a formatted string representation of the number).

See Template Patterns & Modifiers for Numeric Formatting in PostgreSQL for a full list.