PostgreSQL 整型除法要注意

背景

整型除以整型，正常情况下当然得到的应该也是整型。数据库也是这么干的。

但是在数据库应用中，通常业务的需求是得到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::float8;  
 ?column?   
----------  
      0.5  
(1 row)  
  
  
postgres=# select 1/2::numeric;  
        ?column?          
------------------------  
 0.50000000000000000000  
(1 row)  
  
  
postgres=# select 1::money/2;  
 ?column?   
----------  
    $0.50  
(1 row)  

digoal’s 大量PostgreSQL文章入口