背景
整型除以整型，正常情况下当然得到的应该也是整型。数据库也是这么干的。
但是在数据库应用中，通常业务的需求是得到NUMERIC，不能直接把小数干掉。
数据库的行为给用户带来了诸多不便，例如1除以2，如果是整型除法会得到0，然而用户可能要的是0.5。
那么怎么处理呢？
1、建议用户使用float8, money, numeric来存储这种数值。
2、或者在除法时，对操作数进行转换。
操作符
postgres=# \do+ / List of operators Schema | Name | Left arg type | Right arg type | Result type | Function | Description ------------+------+------------------+------------------+------------------+---------------+------------------------------ pg_catalog | / | bigint | bigint | bigint | int8div | divide pg_catalog | / | bigint | integer | bigint | int84div | divide pg_catalog | / | bigint | smallint | bigint | int82div | divide pg_catalog | / | box | point | box | box_div | divide box by point (scale) pg_catalog | / | circle | point | circle | circle_div_pt | divide pg_catalog | / | double precision | double precision | double precision | float8div | divide pg_catalog | / | double precision | real | double precision | float84div | divide pg_catalog | / | integer | bigint | bigint | int48div | divide pg_catalog | / | integer | integer | integer | int4div | divide pg_catalog | / | integer | smallint | integer | int42div | divide pg_catalog | / | interval | double precision | interval | interval_div | divide pg_catalog | / | money | bigint | money | cash_div_int8 | divide pg_catalog | / | money | double precision | money | cash_div_flt8 | divide pg_catalog | / | money | integer | money | cash_div_int4 | divide pg_catalog | / | money | money | double precision | cash_div_cash | divide pg_catalog | / | money | real | money | cash_div_flt4 | divide pg_catalog | / | money | smallint | money | cash_div_int2 | divide pg_catalog | / | numeric | numeric | numeric | numeric_div | divide pg_catalog | / | path | point | path | path_div_pt | divide (rotate/scale path) pg_catalog | / | point | point | point | point_div | divide points (scale/rotate) pg_catalog | / | real | double precision | double precision | float48div | divide pg_catalog | / | real | real | real | float4div | divide pg_catalog | / | smallint | bigint | bigint | int28div | divide pg_catalog | / | smallint | integer | integer | int24div | divide pg_catalog | / | smallint | smallint | smallint | int2div | divide (25 rows)
显示转换
int 转成 numeric或者float8后再除。
例子
postgres=# select 1/2; ?column? ---------- 0 (1 row) 显示转换任意操作数 postgres=# select 1/2.0; ?column? ---------- 0.5 (1 row) postgres=# select 1/2::float8; ?column? ---------- 0.5 (1 row) postgres=# select 1/2::numeric; ?column? ------------------------ 0.50000000000000000000 (1 row)
本篇文章如有帮助到您，请给「翎野君」点个赞，感谢您的支持。
