To format a number as currency in Postgres, you can either convert it to the money data type, or use to_char() to convert it to text that includes the appropriate currency symbol.

This obviously assumes that the number isn’t already stored using the money type.

Below are examples of each of these options.

Convert to Money

Here’s an example of converting a number to the money data type.

SELECT cast(12 as money);

Result:

$12.00

Here’s another example, this time using a larger amount.

SELECT cast(123456.78 as money);

Result:

$123,456.78

Convert from a Floating Point Number

It is recommended against using floating point numbers to handle money due to the potential for rounding errors. However, if you must do it, you can convert to numeric first, then to money.

SELECT '123456.78'::float8::numeric::money;

Result:

$123,456.78

If you try to convert directly from floating point to money, you’ll get an error.

SELECT '123456.78'::float8::money;

Result:

ERROR:  cannot cast type double precision to money

Convert to Text

Here’s an example of using to_char() to convert the value to text with the currency symbol included.

SELECT to_char(12, 'L99D99');

Result:

$ 12.00

Here, both the currency symbol and the decimal place use the current locale.

The format string for to_char() will need to be appropriate for the expected amount.

For example, if the amount could go into the hundreds of thousands, then the following format string would be more appropriate.

SELECT to_char(123456.78, 'L999G999D99');

Result:

$ 123,456.78

In this case I added the G template pattern for a locale-aware group separator (also referred to as a “thousands separator”). I could have used a comma (,), but that would not have been locale-aware.

And I again added the D template pattern for a locale-aware decimal point.

Here’s what happens if I remove those two template patterns from my format string.

SELECT to_char(123456.78, 'L999999');

Result:

$ 123457

Insignificant Zeros

You can use 0 instead of 9 to keep insignificant zeros.

The 9 template pattern drops any insignificant zeros whereas the 0 template pattern does not.

Here’s an example that illustrates the difference between the two.

SELECT 
  to_char(12, 'L9999') AS "L9999",
  to_char(12, 'L0000') AS "L0000";

Result:

 L9999  | L0000  
--------+--------
 $   12 | $ 0012

Fill Mode

There’s a handy template pattern modifier called FM. This is for “fill mode”, and it suppresses trailing zeros and leading blanks.

We can therefore use that modifier to modify the previous example.

SELECT 
  to_char(12, 'LFM9999') AS "LFM9999",
  to_char(12, 'LFM0000') AS "LFM0000";

Result:

 LFM9999 | LFM0000 
---------+---------
 $12     | $0012

Notice that it suppressed the leading blanks between the dollar sign and the number.