Change how numbers are printed in psql

user_967621 0
Viewed 633

How can I set numeric display format in psql?
I have many character varying and double precision columns in the select query. I can use to_char() or round() on each of the numeric columns, but with many columns this makes the code too repetitive.

Is there a shortcut? For example, are there any psql session settings, something like \pset numeric '9.9EEEEE' (I just made it up, don't try to use it). I could not quickly find such settings in the manual: PostgreSQL: Documentation: psql.

Example:

-- Got this:
=# select bar from (values (123456789), (0.123456789), (0.000000123456789)) table_foo (bar);
        bar        
-------------------
         123456789
       0.123456789
 0.000000123456789

-- I have to use this workaround:
=# select to_char(bar, '9.9EEEEE') as bar from (values (123456789), (0.123456789), (0.000000123456789)) table_foo (bar);
   bar    
----------
  1.2e+08
  1.2e-01
  1.2e-07

-- I want this:
-- set some session settings, and then magically:
=# select bar from (values (123456789), (0.123456789), (0.000000123456789)) table_foo (bar);
   bar    
----------
  1.2e+08
  1.2e-01
  1.2e-07
psql floating-point rounding format postgresql
2 Answers

There is not other possibility to specify number of format then specification of numeric locale. Values are formatted on server side already, psql does formatting to final output format (table, html, csv), but the values are formatted, and psql doesn't try to do reformat (numericlocale is an exception). I remember long discussion about possibility to specify output format of boolean type, but still without actual results. psql has not strong functionality for generating rich reports. It should be fast, safe and robust interactive client. Although I would to have some stronger possibility in this area, the modern spreadsheets and reporting tools are for some tasks better.

user_4066910

You could always define your own formatting function, but you'd have to be doing a lot of formatting on a lot of columns for this to be considered an improvement in readability. I wouldn't recommend it for your toy example but if your real queries have dozens of columns it might be neater.

sophia=> create function x(numeric) returns text language sql as $$ select to_char($1, '9.9EEEEE') $$;
CREATE FUNCTION
sophia=> select x(bar) from (values (123456789), (0.123456789), (0.000000123456789)) table_foo (bar);
    x     
----------
  1.2e+08
  1.2e-01
  1.2e-07
(3 rows)

sophia=>
user_3753630
Related