Postgresql 的數值型態

今天要來討論的數值型態.但今天在本站上有一個有意思的討論,

https://ithelp.ithome.com.tw/questions/10195394

主要是討論字元資料型態,我以MySQL 8 為例,做了一些探討,
並順道有提到 Postgresql,可以參考一下.

還是回到 Postgresql 數值型態, 先來參考官方文檔

https://docs.postgresql.tw/the-sql-language/data-types/numeric-types

各型態所使用的 bytes, 範圍在此就不贅述.
首先來看整數.
有 smallint, integer, bigint 三種,在 Postgresql 另外有
int2, int4, int8 分別對應,一種是偏向文字描述風格,一種是以bytes描述的風格,
在一些工具上,或是查詢資料時,都可能會出現.

輸入數值的方法

select int2 '32767', '32767'::int2, 32767::int2;
+-------+-------+-------+
| int2  | int2  | int2  |
+-------+-------+-------+
| 32767 | 32767 | 32767 |
+-------+-------+-------+

前綴法,後綴法 均能使用.

超出範圍會回報錯誤
select 32767::int2 + 1::int2;
ERROR:  22003: smallint out of range

select 1::int2 / 2::int2
     , 2::int2 / 2::int2
     , 3::int2 / 2::int2;

+----------+----------+----------+
| ?column? | ?column? | ?column? |
+----------+----------+----------+
|        0 |        1 |        1 |
+----------+----------+----------+

除法運算需要注意其特性.

select pg_typeof(1::int8 + 1::int8) as "int8"
     , pg_typeof(1::int8 + 1::int4) as "int84"
     , pg_typeof(1::int8 + 1::int2) as "int82";

+--------+--------+--------+
|  int8  | int84  | int82  |
+--------+--------+--------+
| bigint | bigint | bigint |
+--------+--------+--------+

計算結果以大的為基準.

經過前一段時間對 pg_catalog 的探討,相信對以下的查詢,應該較容易理解.

select oprleft::regtype
     , oprname
     , oprright::regtype
     , oprresult::regtype
     , oprcode::regproc
  from pg_catalog.pg_operator
 where oprname = '+'
   and oprleft::regtype = 'bigint'::regtype;
 
+---------+---------+----------+-----------+-------------+
| oprleft | oprname | oprright | oprresult |   oprcode   |
+---------+---------+----------+-----------+-------------+
| bigint  | +       | bigint   | bigint    | int8pl      |
| bigint  | +       | integer  | bigint    | int84pl     |
| bigint  | +       | smallint | bigint    | int82pl     |
| bigint  | +       | inet     | inet      | int8pl_inet |
+---------+---------+----------+-----------+-------------+

先來看前三個, 右邊的型態分別為 bigint, integer, smallint , 使用 + 運算子, 與 bigint 運算,
得到的結果是 bigint, 分別使用 int8pl, int84pl, int82pl 三個函數運算.
這樣就能很好的理解上面的運算結果.

inet 是 Postgrsql 的 Network Address Types. 也可以與 bigint 計算.

接下來看浮點數.

有 real, double precision 兩種, 分別是 4 bytes, 8 bytes

輸入與顯示的方式,可以觀察以下

select '100001'::real as "6 digits"
     , '1000001'::real as "7 digits";

+----------+----------+
| 6 digits | 7 digits |
+----------+----------+
|   100001 |    1e+06 |
+----------+----------+

select '100001.5'::real as "6+1 decimal digits";

+--------------------+
| 6+1 decimal digits |
+--------------------+
|             100002 |
+--------------------+

注意 浮點數是不精準的....

select '100000000000001'::double precision as "15 digits"
     , '1000000000000001'::double precision as "16 digits";

+-----------------+-----------+
|    15 digits    | 16 digits |
+-----------------+-----------+
| 100000000000001 |     1e+15 |
+-----------------+-----------+

select '100000000000001.5'::double precision as "15+1 decimal digits";

+---------------------+
| 15+1 decimal digits |
+---------------------+
|     100000000000002 |
+---------------------+

再來就是厲害的角色上場了... numeric, 可以存放很大範圍的資料,詳細請參考官方文件.
decimal 也是同樣的,會有兩個是因為 ANSI SQL 標準要求.
補充官方文件上沒有強調的點,就是這個是由軟體支援的,不受限於硬體.
相對的,強大的功能是要付出代價的.所以我們在選擇資料型態時,需要做取捨.
以我個人的觀點來說,大多數情境下,正確性的要求是比較重要的.
且硬體進展很快,無需過度擔心速度,過早的優化是不宜的.
要以實際評測為主,切勿只聽信傳言.
而且 Postgresql 的架構很容易擴展,就會有一些很優秀的魔改出現.

既然說 numeric 是厲害的角色,還是先讓他上場一下.
來看個例子, 2零次方 加 2一次方 加 2平方,一路累加到 2的63次方,
可以用 2的64次方減1, 計算得之.

用倍精準度計算

select power(2::double precision, 64::double precision) - 1;

+----------------------+
|       ?column?       |
+----------------------+
| 1.84467440737096e+19 |
+----------------------+
(1 row)

Time: 32.027 ms

用 numeric 計算

select power(2::numeric, 64::numeric) - 1;

+---------------------------------------+
|               ?column?                |
+---------------------------------------+
| 18446744073709551615.0000000000000000 |
+---------------------------------------+
(1 row)

Time: 8.322 ms

因為 2 的次方運算用浮點數,不佔優勢.實際上 numeric 因為極大的數值範圍,平時是要付出代價的.
那既然numeric 威力強大,範圍又大,可是現實中,131072 digits 實在太大了.
所以一些較小範圍的魔改出現了,例如

https://github.com/2ndQuadrant/fixeddecimal

https://pgxn.org/dist/pgdecimal/

https://github.com/vitesse-ftian/pgdecimal

大陸那邊就會有這類的標題出現

震精- PostgreSQL decimal64 decimal128 高效率数值类型扩展

PostgreSQL 用CPU "硬解码" 提升1倍 数值运算能力 助力金融大数据量计算

這年頭沒掛個大數據,都不好意思見人了...