在 PostgreSQL 中格式化双精度
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3122808/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Format double precision in PostgreSQL
提问by ungalnanban
I have a table with 3 columns:
我有一个包含 3 列的表：
customer_name varchar
,account_type varchar
,current_balance double precision
Example values for current_balance:
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?
它以我想要的方式格式化，但在开头添加了一个空格。如何解决这个问题？有没有更好的格式化方法？
回答by leonbloy
As already pointed out in a comment, it's baddesign to use a floating point type (real, double, float) for a money balance. This will lead you to trouble. Use DECIMALinstead.
正如评论中已经指出的那样，使用浮点类型（实数、双精度、浮点数）进行货币余额是糟糕的设计。这会给你带来麻烦。使用DECIMAL来代替。
回答by Charles
You can use trimto remove the extra spaces. With no arguments, it removes only spaces.
您可以使用trim删除多余的空格。没有参数，它只删除空格。
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)
回答by mechanical_meat
to_char(current_balance, 'FM9999999999999999D99')
From the docs:
从文档：
FM: prefix fill mode (suppress padding blanks and zeroes)
FM：前缀填充模式（抑制填充空白和零）
If you want a locale-specific currency symbol, try L:
如果您想要特定于语言环境的货币符号，请尝试L：
to_char(current_balance, 'FML9999999999999999D99')
L: currency symbol (uses locale)
L：货币符号（使用语言环境）
Results from PG 8.4 against column called dblwith value of 12345.678 where id = 1:
来自 PG 8.4 的结果针对dbl值为 12345.678 的列调用，其中 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
[('345.68',)]
