Postgres - always display digits after decimal

Question

A column has a value 0.039

I would always want it to be displayed as 0.0390

How can we achieve this in Postgres?

`TO_CHAR(0.039, '0.9999')` should do it (https://www.postgresql.org/docs/9.3/functions-formatting.html) — Joachim Isaksson, Jan 19 '20 at 09:20
@JoachimIsaksson: you should make that an answer so that this question can be marked as resolved — , Jan 19 '20 at 09:59

Erwin Brandstetter · Answer 1 · 2020-01-19T12:46:00.340

You can use to_char(), like @Joachim provided. But, depending on actual requirements, that may have a number of caveats.

For starters, you may or may not want to prepend the template pattern modifier FM to ...

suppress trailing zeroes and padding blanks

There are also potential issues with rounding, a dangling decimal point, maximum number of displayed digits, etc.

I suggest a cast to numeric(precision, scale) instead. Scale would be 4 in your case, and precision the maximum number of digits you would allow. Just use a big number if you have no particular limit. Like:

SELECT nr::numeric(30,4)

This has the added benefit, that the result is still a numeric data type - as opposed to to_char() which returns text - so sorting with ORDER BY works as expected. Consider:

SELECT nr
     , nr::numeric(30,4) AS cast
     , to_char(nr, 'FM9999999990.9999') AS to_char0
     , to_char(nr, '0.9999') AS to_char1
     , to_char(nr, '0.0000') AS to_char2
     , to_char(nr, 'FM0.9999') AS to_char3
     , to_char(nr, 'FM0.0000') AS to_char4
FROM  (
   VALUES
  (1, numeric '123')
, (2, 1.2)
, (3, 0.00000)
, (4,-0.039)
, (5, 0.039)
, (6, 0.0390)
, (7, 0.039000)
, (8, 0.0391234)
,(9, 123456789.039123456789)
,(10, 123456789.039123456789123456789)
   ) t(id, nr)
ORDER  BY id;

                             nr |           cast | to_char0       | to_char1 | to_char2 | to_char3 | to_char4
------------------------------: | -------------: | :------------- | :------- | :------- | :------- | :-------
                            123 |       123.0000 | 123.           |  #.####  |  #.####  | #.####   | #.####  
                            1.2 |         1.2000 | 1.2            |  1.2000  |  1.2000  | 1.2      | 1.2000  
                        0.00000 |         0.0000 | 0.             |  0.0000  |  0.0000  | 0.       | 0.0000  
                         -0.039 |        -0.0390 | -0.039         | -0.0390  | -0.0390  | -0.039   | -0.0390 
                          0.039 |         0.0390 | 0.039          |  0.0390  |  0.0390  | 0.039    | 0.0390  
                         0.0390 |         0.0390 | 0.039          |  0.0390  |  0.0390  | 0.039    | 0.0390  
                       0.039000 |         0.0390 | 0.039          |  0.0390  |  0.0390  | 0.039    | 0.0390  
                      0.0391234 |         0.0391 | 0.0391         |  0.0391  |  0.0391  | 0.0391   | 0.0391  
         123456789.039123456789 | 123456789.0391 | 123456789.0391 |  #.####  |  #.####  | #.####   | #.####  
123456789.039123456789123456789 | 123456789.0391 | 123456789.0391 |  #.####  |  #.####  | #.####   | #.####

db<>fiddle here

Postgres - always display digits after decimal

1 Answers1