Format double precision in PostgreSQL

Question

I have a table with three columns:

customer_name varchar ,account_type varchar ,current_balance double precision 

Example current_balance values:

  1200
 1500.5
 1500

I want them displayed as follows:

  1200.00
 1500.50
 1500.00

I tried the following query:

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

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

Answer 1

You can use trim to remove extra spaces. With no arguments, it only removes 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 mentioned in the commentary, its poor design uses a floating point type (real, double, floating) for the cash balance . This will lead you into trouble. Use DECIMAL .

Answer 3

 to_char(current_balance, 'FM9999999999999999D99') 

From documents :

FM: prefix padding mode (indenting spaces and zeros)

If you need a locale specific currency symbol try L :

 to_char(current_balance, 'FML9999999999999999D99') 

L: currency symbol (uses locale)

Results from PG 8.4 in a column named dbl with the value 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',)]