Search code examples
postgresqlstring-formatting

Formatting data type real in Postgres

I tried a whole range of patterns with the to_char() function but cannot find the right one.

to_char(price, '99999990D00')

I have two test numbers 0 and 399326, I want 0 to become '0.00' and 399326 to become '399326.00'.

I found out that I needed to add a '9' to my pattern for as many numbers to expect, that is my first concern. When I supply '999999990D99' I get an error message, I suppose this is a too long pattern, but this limits my numbers. This will be a problem. supplying '9990D99' as a pattern to '399326' results in '####.'.

Second of all, I cannot find how to get the two trailing zeros behind the large number, though it works with the '0'. I tried with '999999990D99', '999999990D09' and '999999990D00' but it doesn't seem to work either way.

UPDATE

The solution of Laurenz Albe works with integers, look at my two examples below:

SELECT
    to_char(0, '99999999990D00FM'),
    to_char(1, '99999999990D00FM'),
    to_char(11, '99999999990D00FM'),
    to_char(111, '99999999990D00FM'),
    to_char(1111, '99999999990D00FM'),
    to_char(11111, '99999999990D00FM'),
    to_char(111111, '99999999990D00FM'),
    to_char(1111111, '99999999990D00FM'),
    to_char(11111111, '99999999990D00FM')
WHERE 1=1

outputs:

"0.00"; "1.00"; "11.00"; "111.00"; "1111.00"; "11111.00"; "111111.00"; "1111111.00"; "11111111.00"

As expected.

SELECT
    to_char(0::real, '99999999990D00FM'),
    to_char(1::real, '99999999990D00FM'),
    to_char(11::real, '99999999990D00FM'),
    to_char(111::real, '99999999990D00FM'),
    to_char(1111::real, '99999999990D00FM'),
    to_char(11111::real, '99999999990D00FM'),
    to_char(111111::real, '99999999990D00FM'),
    to_char(1111111::real, '99999999990D00FM'),
    to_char(11111111::real, '99999999990D00FM')
WHERE 1=1

outputs:

"0.00"; "1.00"; "11.00"; "111.00"; "1111.00"; "11111.0"; "111111"; "1111111"; "11111111"

And this is strange, according to the documentation it should work also for the real data type. Is this a bug in Postgres?

Solution

  • Cast the reals to numeric and use the FM modifier:

    SELECT to_char((REAL '123456789')::numeric, '99999999990D00FM');

   to_char    
--------------
 123457000,00
(1 row)

    This will cut off all positions that exceed real's precision.