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?

Answer 1

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 2

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.

Answer 3

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