Format double precision in PostgreSQL

Question

I have a table with 3 columns:

  customer_name varchar
 ,account_type varchar
 ,current_balance double precision

Example values for current_balance:

1200
1500.5
1500

I want them to display like this:

1200.00
1500.50
1500.00

I tried the following query:

SELECT to_char(current_balance,'9999999999999999D99')
  FROM bank;

It formats the way I want but adds a space at the beginning. How to solve this? Is there a better way to format?

Stop right where you are. Carefully [read the section on how doubles are treated in Postgres](http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html#DATATYPE-FLOAT). Doubles are treated as floating point values. Floats are stored and calculated in such a way that they can lose precision. Do not use a floating point value to store a monetary value. Use the `NUMERIC` or `DECIMAL` types. — Charles, Jun 26 '10 at 05:51

score 8 · Accepted Answer · answered Jun 26 '10 at 05:58

8

You can use trim to remove the extra spaces. With no arguments, it removes only spaces.

charles=# SELECT to_char(12345.67,'99999999999999999D99');
        to_char
-----------------------
              12345.67
(1 row)

charles=# SELECT trim(to_char(12345.67,'99999999999999999D99'));
  btrim
----------
 12345.67
(1 row)

answered Jun 26 '10 at 05:58

Charles

50,943
13
104
142

hi Charles.. if my input record is 0 (Zero) that time it gives .00 only. but I want 0.00 how can I do this..? – ungalnanban Jun 28 '10 at 07:26
I don't know, actually. I'm still learning all the ins and outs of Postgres. If all else fails, you can try a `CASE` that check to see if the number is zero, and returns '0.00' if so. – Charles Jun 28 '10 at 15:13
2

Late to the party, but somebody should probably mention '99...0D99' as a possible solution to the 0.00 requirement. – VoiceOfUnreason Jun 01 '12 at 18:39

score 8 · Answer 2 · edited May 23 '17 at 12:17

8

As already pointed out in a comment, it's bad design to use a floating point type (real, double, float) for a money balance. This will lead you to trouble. Use DECIMAL instead.

edited May 23 '17 at 12:17

Community

1
1

answered Jun 28 '10 at 13:28

leonbloy

73,180
20
142
190

Ok thank you... I note your points and here after I will use decimal. – ungalnanban Jun 28 '10 at 14:05
@ungalnanban: This has to do with rounding errors when you don't use decimal, so you better do, because if the accounting-sums are wrong, any float-to-string-conversion will be your lesser problem... – Stefan Steiger Jun 13 '14 at 12:31

mechanical_meat · Answer 3 · 2010-06-26T23:27:21.593

to_char(current_balance, 'FM9999999999999999D99')

From the docs:

FM: prefix fill mode (suppress padding blanks and zeroes)

If you want a locale-specific currency symbol, try L:

to_char(current_balance, 'FML9999999999999999D99')

L: currency symbol (uses locale)

Results from PG 8.4 against column called dbl with value of 12345.678 where id = 1:

>>> import psycopg2
>>> conn = psycopg2.connect(host='localhost', database='scratch', user='',password='')
>>> c = conn.cursor()

>>> c.execute("select to_char(dbl, '9999999999999999D99') from practice where id = 1;")
>>> c.fetchall() # with padding
[('            12345.68',)]

>>> c.execute("select to_char(dbl, 'FM9999999999999999D99') from practice where id = 1;")
>>> c.fetchall() # no padding
[('12345.68',)]

>>> c.execute("select to_char(dbl, 'FML9999999999999999D99') from practice where id = 1;")
>>> c.fetchall() # with locale-specific currency symbol
[('$12345.68',)]

Format double precision in PostgreSQL

3 Answers3

Linked