Format double precision in PostgreSQL

Question

I have a table with 3 columns. customer_name varchar, account_typevarchar , current_balance_amount double precision.

this table having the following records.

current_balance
1200
1500.5
1500

If I execute the select query the above records are displayed. I want the current_balance amount as like the following formate.

current_balance
1200.00
1500.50
1500.00

for that I used the following query:

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

the above query giving the formatted output but it adds space at beginning how to solve this is their any better way to formate the amount?

Answer 1

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',)]

Answer 2

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)

Answer 3

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.