PostgreSQL DBA(116) - pgAdmin(Don't do this：Don't use money&serial)

no zuo no die系列，來自于pg的wiki。
這一節(jié)的內(nèi)容是：不要使用money。
理由是:

money
It’s a fixed-point type, implemented as a machine int, so arithmetic with it is fast. But it doesn’t handle fractions of a cent (or equivalents in other currencies), it’s rounding behaviour is probably not what you want.
It doesn’t store a currency with the value, rather assuming that all money columns contain the currency specified by the database’s lc_monetary locale setting. If you change the lc_monetary setting for any reason, all money columns will contain the wrong value. That means that if you insert ‘$10.00’ while lc_monetary is set to ‘en_US.UTF-8’ the value you retrieve may be ‘10,00 Lei’ or ‘￥1,000’ if lc_monetary is changed.
Storing a value as a numeric, possibly with the currency being used in an adjacent column, might be better.

原因是money類型是定點(diǎn)類型，在計(jì)算機(jī)中通過機(jī)器int類型實(shí)現(xiàn)，取整行為可能不符合期望，同時該字段并沒有存儲貨幣單位，取決于數(shù)據(jù)庫的lc_monetary設(shè)定，如果計(jì)量單位變化那么該值可能會存在問題，因此使用numeric存儲數(shù)值額外使用其他字段存儲貨幣單位。

[local]:5432 pg12@testdb=# drop table if exists t_money;
NOTICE:  table "t_money" does not exist, skipping
DROP TABLE
Time: 35.404 ms
[local]:5432 pg12@testdb=# create table t_money(id int,c1 money);
CREATE TABLE
Time: 133.600 ms
[local]:5432 pg12@testdb=# insert into t_money(id,c1) values(1,112343.01);
INSERT 0 1
Time: 0.929 ms
[local]:5432 pg12@testdb=# insert into t_money(id,c1) values(2,112343.31234);
INSERT 0 1
Time: 0.640 ms
[local]:5432 pg12@testdb=# insert into t_money(id,c1) values(3,112343.50);
INSERT 0 1
Time: 0.599 ms
[local]:5432 pg12@testdb=# insert into t_money(id,c1) values(4,112343.99);
INSERT 0 1
Time: 0.523 ms
[local]:5432 pg12@testdb=# insert into t_money(id,c1) values(5,112343.3199);
INSERT 0 1
Time: 0.483 ms
[local]:5432 pg12@testdb=# insert into t_money(id,c1) values(6,112343.3150);
INSERT 0 1
Time: 0.533 ms
[local]:5432 pg12@testdb=# insert into t_money(id,c1) values(7,112343.3101);
INSERT 0 1
Time: 0.531 ms
[local]:5432 pg12@testdb=#

查詢數(shù)據(jù)，實(shí)際的數(shù)據(jù)是四舍五入，保留兩位小數(shù)

[local]:5432 pg12@testdb=# select * from t_money;
 id |     c1      
----+-------------
  1 | $112,343.01
  2 | $112,343.31
  3 | $112,343.50
  4 | $112,343.99
  5 | $112,343.32
  6 | $112,343.32
  7 | $112,343.31
(7 rows)
Time: 0.321 ms

修改數(shù)據(jù)庫參數(shù)的貨幣設(shè)置為zh_CN

[local]:5432 pg12@testdb=# 
[local]:5432 pg12@testdb=# show lc_monetary;
 lc_monetary 
-------------
 zh_CN.UTF-8
(1 row)
Time: 5.212 ms
[local]:5432 pg12@testdb=# select * from t_money;
 id |      c1      
----+--------------
  1 | ￥112,343.01
  2 | ￥112,343.31
  3 | ￥112,343.50
  4 | ￥112,343.99
  5 | ￥112,343.32
  6 | ￥112,343.32
  7 | ￥112,343.31
(7 rows)
Time: 2.092 ms
[local]:5432 pg12@testdb=#

美鈔成了人民幣。

參考資料
Don’t Do This