在一般的運算中, 分數會使用浮點數來近似.
PostgreSQL 有許多強大的 extension,提供了許多extension,讓我們更方便.
這個 extension 的作者同時也是 PostgREST的作者.

https://github.com/begriffs/pg_rational

安裝方式很簡單,解壓, make , make install

然後在 資料庫中,使用 create extension 安裝.

我是使用指定 schema 的方式,安裝在 public schema

create extension pg_rational with schema public;
commit;

--
接著來看安裝以後, 新增了哪些運算子.查詢了 pg_catalog, 語法在此從略.
有興趣的可以看我以前寫的介紹 pg_catalog 及 hstore, array 等.

+-----------------------------------+--------------+
|               expr                |   oprcode    |
+-----------------------------------+--------------+
|   rational >=   ration = boolean  | rational_ge  |
|   rational >    ration = boolean  | rational_gt  |
|   rational =    ration = boolean  | rational_eq  |
|   rational <>   ration = boolean  | rational_ne  |
|   rational <=   ration = boolean  | rational_le  |
|   rational <    ration = boolean  | rational_lt  |
|   rational /    ration = rational | rational_div |
|            -    ration = rational | rational_neg |
|   rational -    ration = rational | rational_sub |
|   rational +    ration = rational | rational_add |
|   rational *    ration = rational | rational_mul |
+-----------------------------------+--------------+
(11 rows)

新增的函數

+--------+-----------------------+-----------+----------+--------------------+-------------+
| schema |         proc          |   kind    | language |     arguments      | return_type |
+--------+-----------------------+-----------+----------+--------------------+-------------+
| public | max                   | AGGREGATE | internal | rational           | rational    |
| public | min                   | AGGREGATE | internal | rational           | rational    |
| public | rational_add          | FUNCTION  | c        | rational, rational | rational    |
| public | rational_cmp          | FUNCTION  | c        | rational, rational | int4        |
| public | rational_div          | FUNCTION  | c        | rational, rational | rational    |
| public | rational_eq           | FUNCTION  | c        | rational, rational | bool        |
| public | rational_ge           | FUNCTION  | c        | rational, rational | bool        |
| public | rational_gt           | FUNCTION  | c        | rational, rational | bool        |
| public | rational_hash         | FUNCTION  | c        | rational           | int4        |
| public | rational_intermediate | FUNCTION  | c        | rational, rational | rational    |
| public | rational_larger       | FUNCTION  | c        | rational, rational | rational    |
| public | rational_le           | FUNCTION  | c        | rational, rational | bool        |
| public | rational_lt           | FUNCTION  | c        | rational, rational | bool        |
| public | rational_mul          | FUNCTION  | c        | rational, rational | rational    |
| public | rational_ne           | FUNCTION  | c        | rational, rational | bool        |
| public | rational_neg          | FUNCTION  | c        | rational           | rational    |
| public | rational_out          | FUNCTION  | c        | rational           | cstring     |
| public | rational_out_float    | FUNCTION  | c        | rational           | float8      |
| public | rational_send         | FUNCTION  | c        | rational           | bytea       |
| public | rational_simplify     | FUNCTION  | c        | rational           | rational    |
| public | rational_smaller      | FUNCTION  | c        | rational, rational | rational    |
| public | rational_sub          | FUNCTION  | c        | rational, rational | rational    |
| public | sum                   | AGGREGATE | internal | rational           | rational    |
| public | tuple_to_rational     | FUNCTION  | sql      | ratt               | rational    |
+--------+-----------------------+-----------+----------+--------------------+-------------+
(24 rows)

可以觀察到有部分函數是 operator 呼叫的.
兩部分的差異為

+-----------------------+
|         proc          |
+-----------------------+
| max                   |
| min                   |
| rational_cmp          |
| rational_hash         |
| rational_intermediate |
| rational_larger       |
| rational_out          |
| rational_out_float    |
| rational_send         |
| rational_simplify     |
| rational_smaller      |
| sum                   |
| tuple_to_rational     |
+-----------------------+
(13 rows)

max, min, sum 為聚合函數.

-------
接著來看一些基本使用

select 1::rational / 3 * 3 = 1
     , 1::rational / 4 * 4 = 1
     , 1::rational / 5 * 5 = 1;
+----------+----------+----------+
| ?column? | ?column? | ?column? |
+----------+----------+----------+
| t        | t        | t        |
+----------+----------+----------+
(1 row)

Time: 3.790 ms

select '1/3'::rational + '2/7'
     , '2/3'::rational - '1/3'
     , rational_simplify('2/3'::rational - '1/3');
     
+----------+----------+-------------------+
| ?column? | ?column? | rational_simplify |
+----------+----------+-------------------+
| 13/21    | 3/9      | 1/3               |
+----------+----------+-------------------+
(1 row)

Time: 0.715 ms

select '-1/2'::rational::float
     , 0.263157894737::float::rational
     , (1.0/3.0)::float::rational
     , (1.0/4.0)::float::rational;

+--------+----------+----------+----------+
| float8 | rational | rational | rational |
+--------+----------+----------+----------+
|   -0.5 | 5/19     | 1/3      | 1/4      |
+--------+----------+----------+----------+
(1 row)

Time: 2.526 ms

select (i,i+1)::ratt 
     , 0 + (i,i+1)::ratt
     , 1 + (i,i+1)::ratt
     , (0.5)::float::rational + (i,i+1)::ratt
  from generate_series(1,5) as i;

+-------+----------+----------+----------+
|  row  | ?column? | ?column? | ?column? |
+-------+----------+----------+----------+
| (1,2) | 1/2      | 3/2      | 4/4      |
| (2,3) | 2/3      | 5/3      | 7/6      |
| (3,4) | 3/4      | 7/4      | 10/8     |
| (4,5) | 4/5      | 9/5      | 13/10    |
| (5,6) | 5/6      | 11/6     | 16/12    |
+-------+----------+----------+----------+
(5 rows)

Time: 0.892 ms

簡單介紹到此,先告一段落.