PostgreSQL整型相除規範

標籤

PostgreSQL , int , 整型 , 除法

---

背景

整型除以整型，正常情況下當然得到的應該也是整型。資料庫也是這麼幹的。

但是在資料庫應用中，通常業務的需求是得到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)