Formatting data type real in Postgres

Question

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?

Laurenz Albe · Accepted Answer · 2018-11-09 09:21:23Z

1

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.

edited Nov 9, 2018 at 9:21

answered Nov 8, 2018 at 19:29

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

DeniseMeander Over a year ago

Thank you for reformatting and answering my question! Your solution works on integers but not on reals. I updated my question adding two examples.

DeniseMeander Over a year ago

This works only for whole numbers, look at this example: to_char((1234567.89::real)::numeric(12,2), '99999999990D00FM')

Laurenz Albe Over a year ago

No, that last example is handled correctly. The places than become zero are beyond what real can express.

DeniseMeander Over a year ago

The output of this last example is "1234570.00", you're telling me that a real cannot contain the number 1234567.89?

Laurenz Albe Over a year ago

Yes, exactly. At least not precisely enough. You'd need double precision for that. The cast to numeric rounds away digits that are unreliable.

|

Collectives™ on Stack Overflow

Formatting data type real in Postgres

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related