PostgreSQL 資料型別 • PostgreSQL 的藝術

PostgreSQL 內建的資料型別非常多。下面的查詢已經把範圍限縮到應用程式開發者直接會關心的型別，仍然列出 72 種：

select nspname, typname
    from      pg_type t
         join pg_namespace n
           on n.oid = t.typnamespace
   where nspname = 'pg_catalog'
     and typname !~ '(^_|^pg_|^reg|_handler$)'
order by nspname, typname;

順帶一提，加上 TABLESAMPLE bernoulli(20) 可以隨機抽樣約 20% 的型別玩玩（這是 select 文件頁裡記載的 PostgreSQL 功能）——不過本書挑選型別當然不是用抽樣決定的。以下逐一介紹重點型別。

Boolean#

Boolean 在前面「三值邏輯」一節已經談過：SQL 的布林真值表包含 true、false 與 null 三種值，例如 true = null 的結果是 null、null = null 的結果也是 null。

元組屬性當然可以是 Boolean，而且 PostgreSQL 為它們提供了專用聚合函式：

  select year,
         format('%s %s', forename, surname) as name,
         count(*) as ran,
         count(*) filter(where position = 1) as won,
         count(*) filter(where position is not null) as finished,
         sum(points) as points
    from       races
          join results using(raceid)
          join drivers using(driverid)
group by year, drivers.driverid
  having bool_and(position = 1) is true
order by year, points desc;

bool_and() 在所有布林輸入皆為 true 時回傳 true。它跟每個聚合函式一樣預設會默默略過 null，所以 bool_and(position = 1) 篩出的是「該賽季完賽的比賽全部獲勝」的 F1 車手。

 year │        name         │ ran │ won │ finished │ points
══════╪═════════════════════╪═════╪═════╪══════════╪════════
 1950 │ Juan Fangio         │   7 │   3 │        3 │     27
 1950 │ Johnnie Parsons     │   1 │   1 │        1 │      9
 1952 │ Alberto Ascari      │   7 │   6 │        6 │   53.5
 ...
 1968 │ Jim Clark           │   1 │   1 │        1 │      9
(17 rows)

若想改成「該賽季參加的比賽全部完賽且獲勝」，要寫 having bool_and(position is not distinct from 1)，結果就只剩該季只出賽一場的車手。

關於 Boolean 的重點是搭配的運算子：

= 的行為跟你直覺想的不一樣
測試 true、false、null 字面值時用 is，不要用 =
需要時記得使用 is distinct from 與 is not distinct from
Boolean 可以用 bool_and、bool_or 聚合

SQL 的 Boolean 有三種值：true、false 與 null，而 null 的行為完全是特例規則（ad-hoc）——要嘛把它背起來，要嘛記得隨時驗證你的假設。延伸閱讀：PostgreSQL 貢獻者 Jeff Davis 的 What is the deal with NULLs?。

字元與文字#

PostgreSQL 的字元／文字型別都記載於文件的 character types 章節：

對 PostgreSQL 而言 text 與 varchar 是同一件事，character varying 是 varchar 的別名。
寫 varchar(15) 基本上等於告訴 PostgreSQL：管理一個帶有「15 個字元」check 約束的 text 欄位——而且 PostgreSQL 即使在 Unicode 編碼下也懂得正確計算字元數。

文字處理函式非常豐富（見 string functions and operators 文件章節）：overlay()、substring()、position()、trim()，聚合函式 string_agg()，以及正規表達式（regular expression）函式，包括威力強大的 regexp_split_to_table()。

除了傳統的 like／ilike 與 SQL 標準的 similar to，PostgreSQL 內建完整的 regexp 引擎，主要運算子是 ~，加上否定與忽略大小寫的變體共四個：~、!~、~*、!~*。
藉由 trigram 擴充套件 pg_trgm，PostgreSQL 還支援對正規表達式查詢建立索引。

regexp 切割函式在「一個欄位以半規格化方式塞了多項資訊」的髒 schema 上特別好用。以開放資料「行星檔案庫（Archives de la Planète）」為例，其 themes 欄位以逗號分隔多個主題，主題內又以 > 分隔兩層分類，例如 id 為 IF39599 的照片：

themes │ Habillement > Habillement traditionnel,Etres humains > Homme,
       │ Etres humains > Portrait,Relations internationales > Présence étrangère

先用 regexp 把 themes 切成一列一主題，再切出保持在一起的分類陣列，最後投影成正式欄位：

with categories(id, categories) as
  (
     select id,
            regexp_split_to_array(
              regexp_split_to_table(themes, ','),
              ' > ')
            as categories
       from opendata.archives_planete
  )
 select id,
        categories[1] as category,
        categories[2] as subcategory
   from categories
  where id = 'IF39599';

   id    │         category          │       subcategory
═════════╪═══════════════════════════╪══════════════════════════
 IF39599 │ Habillement               │ Habillement traditionnel
 IF39599 │ Etres humains             │ Homme
 IF39599 │ Etres humains             │ Portrait
 IF39599 │ Relations internationales │ Présence étrangère
(4 rows)

同一個 CTE 改成 group by rollup(category, subcategory) 加上 count(*)，就能得到整個資料集依分類的分布（175 列）；除了 rollup 也可以用 cube 或自行指定分組維度。

匯入 PostgreSQL 之後才清理資料，正是傳統 ETL（extract, transform, load）與更強大的 ELT（extract, load, transform）的差別：後者讓你用一個資料處理語言——SQL——來轉換資料。
在 ELT 作業中，接下來會建立 categories 目錄表與關聯表做正規化；此處主題是文字處理，就不展開。

談到進階字串比對，還必須提 PostgreSQL 的全文檢索（full text search）：支援文件、進階查詢、排名、標示、可插拔的解析器、字典與詞幹擷取器、同義詞與同義詞典，而且全部可以配置。這是另一本書的題材，需要時請閱讀官方文件與網路資源。

伺服器編碼與客戶端編碼#

編碼（encoding）是字元在位元與位元組層次的特定表示法：ASCII 中字母 A 是 7 位元的 1000001（十進位 65、十六進位 41）。用 psql 的 \l 可以看到資料庫的編碼：

   Name    │ Owner    │ Encoding │   Collate   │    Ctype    │ …
═══════════╪══════════╪══════════╪═════════════╪═════════════╪═
 f1db      │ dim      │ UTF8     │ en_US.UTF-8 │ en_US.UTF-8 │ …
 template0 │ postgres │ UTF8     │ en_US.UTF-8 │ en_US.UTF-8 │ …
 ...

這裡的編碼是 UTF8——當今最好的選擇。

如果資料庫是 UTF8，而應用程式（或程式語言）不太會處理 Unicode，可以透過 client_encoding 設定請 PostgreSQL 即時在伺服器端編碼與客戶端編碼之間轉換所有資料。但要注意：不是所有伺服器編碼與客戶端編碼的組合都合理——當伺服器端資料含有客戶端編碼裝不下的文字（西里爾、中文、日文、阿拉伯文等非拉丁文字）時，PostgreSQL 會直接報錯。

延伸範例：多語言 hello 資料與 latin1 轉換錯誤

從 Emacs 的 M-x view-hello-file 取得一張以 UTF8 編碼、涵蓋多種語言與文字系統的「hello」對照表：

        language         │           hello
═════════════════════════╪═════════════════════════════
 Czech (čeština)         │ Dobrý den
 English /ˈɪŋɡlɪʃ/       │ Hello
 French (français)       │ Bonjour / Salut
 Georgian (ქართველი)     │ გამარჯობა
 Greek (ελληνικά)        │ Γειά σας
 Mathematics             │ ∀ p ∈ world • hello p □
 Russian (русский)       │ Здра́вствуйте!
 Japanese (日本語)        │ こんにちは
 Chinese (中文,普通话,汉语) │ 你好
 ...

這樣的資料當然無法轉成 latin1 送出：

yesql# set client_encoding to latin1;
SET
yesql# select * from hello where language ~ 'Georgian';
ERROR: character with byte sequence 0xe1 0x83 0xa5 in encoding "UTF8"
has no equivalent in encoding "LATIN1"
yesql# reset client_encoding ;
RESET

可以的話就用 UTF-8，日子會簡單得多。要注意 Unicode 讓文字比較與排序變成相當昂貴的操作——但「快但錯」不是選項，所以我們還是用 Unicode 文字。

數字#

PostgreSQL 提供多種數字型別（見 numeric types 文件章節）：

integer：32 位元有號整數
bigint：64 位元有號整數
smallint：16 位元有號整數
numeric：任意精度數字
real：32 位元浮點數，6 位十進位精度
double precision：64 位元浮點數，15 位十進位精度

前面提過 SQL 查詢系統是靜態型別的：PostgreSQL 必須在規劃與執行前確立查詢輸入與結果集中每個欄位的型別。對數字而言，每個數字字面值的型別都得在解析階段推導出來。例如統計「桿位起跑且獲勝」的查詢會用到 grid = 1 and position = 1，字面值 1 可能是 smallint、integer、bigint 甚至 numeric——已知 grid 與 position 是 bigint 這件事會影響解析時的選擇。同時影響選擇的還有 = 運算子本身，左運算元為 bigint 時就有三個變體：

  select oprname,
         oprcode::regproc,
         oprleft::regtype,
         oprright::regtype,
         oprresult::regtype
    from pg_operator
   where oprname = '='
     and oprleft::regtype = 'bigint'::regtype;

 oprname │ oprcode │ oprleft │ oprright │ oprresult
═════════╪═════════╪═════════╪══════════╪═══════════
 =       │ int8eq  │ bigint  │ bigint   │ boolean
 =       │ int84eq │ bigint  │ integer  │ boolean
 =       │ int82eq │ bigint  │ smallint │ boolean
(3 rows)

若非 PostgreSQL 支援整數型別間所有組合的 =，我們就得在所有查詢裡寫修飾字面值：where grid = bigint '1'。

比較數字的內部運算子與支援函式會組合爆炸，這正是 PostgreSQL 專案選擇讓數字型別數量最小化的原因：每多一種型別，對查詢規劃時間與內部資料結構大小的衝擊都很大。這也是 PostgreSQL 沒有無號（unsigned）數字型別的原因。

浮點數#

在認真使用浮點數之前，請先讀 What Every Programmer Should Know About Floating-Point Arithmetic。簡單說：有些數在十進位表示不了（如 1/3），二進位也有另一組表示不了的數——例如 1/5 與 1/10。

使用 real 或 double precision 前要清楚自己在做什麼，而且永遠不要用它們處理金錢。請改用任意精度的 numeric，或以整數為基礎的金額表示法。

序列與 serial 偽型別#

smallserial、serial 與 bigserial 其實是偽型別（pseudo type）：解析器認得語法，然後把它轉換成完全不同的東西。官方文件寫得很清楚：

CREATE TABLE tablename (
    colname SERIAL
);

等價於：

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

序列（sequence）是 SQL 標準涵蓋的物件，也是 SQL 中唯一具非交易性行為的物件——這是故意的：多個 session 可以並行取得下一個序號，不必等到 commit 才決定能否保留。
文件明言：序列以 bigint 算術為基礎，範圍就是 8 位元組整數的範圍。

create table seq(id serial);
select setval('public.seq_id_seq'::regclass, 2147483647);
insert into public.seq values (default);
ERROR: integer out of range
若用 serial 而非 bigserial，這可能在生產環境真實上演。若必須把欄位限制在 4 位元組整數又需要序列，就得針對「序列是 8 位元組、欄位只有 4 位元組」這件事建立維護策略。

通用唯一識別碼：UUID#

UUID 是用來識別資訊的 128 位元數字（另一個稱呼是 GUID）。PostgreSQL 支援 UUID 的儲存與處理；若要在資料庫端產生 UUID，需安裝 contrib 套件中的 uuid-ossp 擴充：

create extension "uuid-ossp";

select uuid_generate_v4()
  from generate_series(1, 10) as t(x);

           uuid_generate_v4
══════════════════════════════════════
 fbb850cc-dd26-4904-96ef-15ad8dfaff07
 0ab19b19-c407-410d-8684-1c3c7f978f49
 ...
(10 rows)

即使 UUID 是在應用程式端產生的，在 PostgreSQL 中仍應以真正的 UUID 型別管理：PostgreSQL 儲存的是 128 位元（16 位元組）的二進位值，遠小於文字表示：

select pg_column_size(uuid 'fbb850cc-dd26-4904-96ef-15ad8dfaff07')
       as uuid_bytes,
       pg_column_size('fbb850cc-dd26-4904-96ef-15ad8dfaff07')
       as uuid_string_bytes;

 uuid_bytes │ uuid_string_bytes
════════════╪═══════════════════
         16 │                37
(1 row)

至於「該不該用 UUID 當資料庫 schema 的識別碼」，下一章再談。

Bytea 與位元字串#

PostgreSQL 能儲存與處理原始二進位值（見 Binary Data Types 文件章節），單一欄位上限約 1 GB（其中 8 位元組是標頭）。

PostgreSQL 沒有 chunk API：只要查詢輸出包含該欄位，就會一次抓取完整內容——從磁碟載入記憶體、推過網路、在客戶端整包處理，所以不一定是最佳解。
另一面：存進 PostgreSQL 的二進位內容自動納入線上備份與還原方案，而且線上備份是交易性的。若你需要具交易性質的二進位內容，bytea 可能正是你要的。

日期時間與時區#

處理日期、時間與時區極其複雜，可讀 Erik Naggum 的 The Long, Painful History of Time；PostgreSQL 側的完整資訊在 Date/Time Types、Data Type Formatting Functions、Date/Time Functions and Operators 三個文件章節。

第一個問題：timestamp 要不要帶時區？答案很簡單——永遠使用 timestamp WITH time zone（timestamptz）。「存時區會增加儲存與記憶體開銷」是迷思：pg_column_size 顯示兩者都是 8 位元組。PostgreSQL 內部用 bigint 儲存 timestamp，磁碟與記憶體格式帶不帶時區都一樣（原始碼中就是 typedef int64 Timestamp; typedef int64 TimestampTz;）。

文件說明其運作方式：timestamptz 內部一律以 UTC 儲存；輸入值若標明時區就按該時區的位移轉成 UTC，若沒標就假設為系統 TimeZone 參數所指的時區。也就是說 PostgreSQL 不會儲存資料原本的時區，只是在輸入與輸出時做轉換——就像文字的 client_encoding 一樣。用一段腳本觀察：

begin;

create table tstz(ts timestamp, tstz timestamptz);

set timezone to 'Europe/Paris';
insert into tstz values(now(), now());

set timezone to 'Pacific/Tahiti';
insert into tstz values(now(), now());

set timezone to 'Europe/Paris';
table tstz;

set timezone to 'Pacific/Tahiti';
table tstz;

commit;

腳本輸出：同一時刻在兩個時區下的樣貌

大溪地（Tahiti）是太平洋上屬於法國的島嶼，所以這其實是從一個法國值換到另一個法國值：

set timezone to 'Europe/Paris';
select now();
 2017-08-19 14:22:11.802755+02

set timezone to 'Pacific/Tahiti';
select now();
 2017-08-19 02:22:11.802755-10

set timezone to 'Europe/Paris';
table tstz;
             ts             │             tstz
════════════════════════════╪═══════════════════════════════
 2017-08-19 14:22:11.802755 │ 2017-08-19 14:22:11.802755+02
 2017-08-19 02:22:11.802755 │ 2017-08-19 14:22:11.802755+02

set timezone to 'Pacific/Tahiti';
table tstz;
             ts             │             tstz
════════════════════════════╪═══════════════════════════════
 2017-08-19 14:22:11.802755 │ 2017-08-19 02:22:11.802755-10
 2017-08-19 02:22:11.802755 │ 2017-08-19 02:22:11.802755-10

從這個實驗可以看到三件事：

now() 在同一交易內永遠回傳相同的 timestamp；想看時鐘前進要用 clock_timestamp()。
改變 timezone 設定後，PostgreSQL 以所選時區輸出 timestamp。若應用程式的使用者分布在不同時區、想以各自偏好的當地時間顯示，只要在處理 timestamp 前於應用程式碼中 set timezone，剩下的苦工 PostgreSQL 全包。
tstz 欄位知道兩筆插入其實是同一個時間點、只是從世界不同地點看；ts 欄位則讓這兩筆變得無從比較。也因為不儲存輸入時區，光看 tstz 表無法知道兩筆資料「時間相同、地點不同」。

本節開頭提到的 The Long, Painful History of Time 值得一讀，其論點與此呼應：要在時空中定位一個事件，時間與地點缺一不可，但一般溝通中時區幾乎總被略去，多數人甚至不清楚自己的時區與標準參考的關係——所以「負責任的電腦人」必須正視這個問題，抗拒省略脈絡的衝動。

輸入 timestamp 值最簡單的方式是 ISO 格式；時區通常交給 timezone session 參數處理，需要時也可以直接寫進值裡：

select timestamptz '2017-01-08 04:05:06',
       timestamptz '2017-01-08 04:05:06+02';

在 insert／update 時不必加型別修飾：PostgreSQL 已知道目標欄位的型別，會據此解析 DML 敘述中的字面值。若用例只需要日期，就用 date 型別；date 與 timestamptz 可以直接比較，也可以在 date 上疊加時間位移構造出 timestamp。

時間區間#

PostgreSQL 在 time、date、timestamptz 之外還實作了 interval 型別，用來描述一段持續時間，例如一個月、兩週、甚至一毫秒：

set intervalstyle to postgres;

select interval '1 month',
       interval '2 weeks',
       2 * interval '1 week',
       78389 * interval '1 ms';

 interval │ interval │ ?column? │   ?column?
══════════╪══════════╪══════════╪══════════════
 1 mon    │ 14 days  │ 14 days  │ 00:01:18.389
(1 row)

intervalstyle 有多種設定，互動式 psql 中 postgres_verbose 相當友善（輸出如 @ 1 min 18.389 secs）。

一個月是多長？視是哪個月而定，而 PostgreSQL 知道：

select d::date as month,
       (d + interval '1 month' - interval '1 day')::date as month_end,
       (d + interval '1 month')::date as next_month,
       (d + interval '1 month')::date - d::date as days
  from generate_series(
                        date '2017-01-01',
                        date '2017-12-01',
                        interval '1 month'
                      )
       as t(d);

   month    │ month_end  │ next_month │ days
════════════╪════════════╪════════════╪══════
 2017-01-01 │ 2017-01-31 │ 2017-02-01 │   31
 2017-02-01 │ 2017-02-28 │ 2017-03-01 │   28
 2017-03-01 │ 2017-03-31 │ 2017-04-01 │   31
 ...
 2017-12-01 │ 2017-12-31 │ 2018-01-01 │   31
(12 rows)

interval 附著在 date 或 timestamp 上時，天數會依你挑的日曆位置自動調整（算出二月底變得非常容易）；單獨存在時，一個月視為 30 天。
PostgreSQL 的日曆實作非常好，用它！

日期時間的處理與查詢#

資料以 timestamptz 妥善儲存後，各種處理都能實作。這裡的範例資料集是 git 歷史：PostgreSQL 與 pgloader 專案的 git log（自訂格式加後處理）被載入 commitlog 表，欄位 ats、cts 分別是 author commit timestamp 與 committer commit timestamp，subject 是 commit 訊息第一行。

有了 timestamp 就能做時間維度的報表。例如兩個專案每年收到多少 commit：

  select extract(year from ats) as year,
         count(*) filter(where project = 'postgresql') as postgresql,
         count(*) filter(where project = 'pgloader') as pgloader
    from commitlog
group by year
order by year;

輸出顯示 PostgreSQL 專案超過 20 年的持續活動（1996 年 876 筆起，多數年份一至三千筆），pgloader 則活躍度較低。

再看依星期幾的 commit 分布，猜測貢獻者是上班時間投入還是週末投入：

  select extract(isodow from ats) as dow,
         to_char(ats, 'Day') as day,
         count(*) as commits,
         round(100.0*count(*)/sum(count(*)) over(), 2) as pct,
         repeat('■', (100*count(*)/sum(count(*)) over())::int) as hist
    from commitlog
   where project = 'postgresql'
group by dow, day
order by dow;

 dow │    day    │ commits │  pct  │       hist
═════╪═══════════╪═════════╪═══════╪═══════════════════
   1 │ Monday    │    6552 │ 15.14 │ ■■■■■■■■■■■■■■■
   2 │ Tuesday   │    7164 │ 16.55 │ ■■■■■■■■■■■■■■■■■
   3 │ Wednesday │    6477 │ 14.96 │ ■■■■■■■■■■■■■■■
   4 │ Thursday  │    7061 │ 16.31 │ ■■■■■■■■■■■■■■■■
   5 │ Friday    │    7008 │ 16.19 │ ■■■■■■■■■■■■■■■■
   6 │ Saturday  │    4690 │ 10.83 │ ■■■■■■■■■■■
   7 │ Sunday    │    4337 │ 10.02 │ ■■■■■■■■■■
(7 rows)

看來 PostgreSQL 的 committer 想工作就工作，但週末明顯少一點——這個專案很幸運，有一群被聘僱來全職開發的堅實 committer 團隊。

延伸範例：author 與 committer 時間差的百分位數統計

比較 ats 與 cts 差多少，用 percentile_cont 搭配陣列一次算出多個百分位：

with perc_arrays as
  (
       select project,
              avg(cts-ats) as average,
              percentile_cont(array[0.5, 0.9, 0.95, 0.99])
                  within group(order by cts-ats) as parr
         from commitlog
        where ats <> cts
     group by project
  )
 select project, average,
        parr[1] as median,
        parr[2] as "%90th",
        parr[3] as "%95th",
        parr[4] as "%99th"
   from perc_arrays;

─[ RECORD 1 ]───────────────────────────────────
project │ pgloader
average │ @ 4 days 22 hours 7 mins 41.18 secs
median  │ @ 5 mins 21.5 secs
%90th   │ @ 1 day 20 hours 49 mins 49.2 secs
%95th   │ @ 25 days 15 hours 53 mins 48.15 secs
%99th   │ @ 169 days 24 hours 33 mins 26.18 secs
═[ RECORD 2 ]═══════════════════════════════════
project │ postgres
average │ @ 1 day 10 hours 15 mins 9.706809 secs
median  │ @ 2 mins 4 secs
%90th   │ @ 1 hour 46 mins 13.5 secs
%95th   │ @ 1 day 17 hours 58 mins 7.5 secs
%99th   │ @ 40 days 20 hours 36 mins 43.1 secs

報表是 SQL 的強項用例，應用程式也會送出更經典的查詢，例如列出某一天的 commit：

\set day '2017-06-01'

  select ats::time,
         substring(hash from 1 for 8) as hash,
         substring(subject from 1 for 40) || '…' as subject
    from commitlog
   where project = 'postgresql'
     and ats >= date :'day'
     and ats < date :'day' + interval '1 day'
order by ats;

這裡會很想用 between，但 between 同時包含上下界，那就得把上界算成當天的最後一瞬。改用明確的 >= 與 <，永遠只需要計算「一天的開始」，簡單且 PostgreSQL 支援良好；而且明確界限讓查詢只需一個日期字面值——應用程式只要傳一個參數。

資料型別格式化函式也很多。上面把 timestamptz 轉型成 time，也可以改用 to_char() 搭配 set lc_time to 'fr_FR'，得到法文在地化輸出（如 Jeudi 01 Juin, 01am）。花點時間熟悉 PostgreSQL 內建的日期時間支援吧——date_trunc() 等實用函式這裡沒展示，還有更多寶藏可挖。

雖然現代程式語言多半也有同類功能，但把這類處理放在 PostgreSQL 做，在幾種情境下很合理：

SQL 邏輯或過濾條件依賴處理結果（例如按週分組）
多個應用程式共用同一套邏輯時，分享一條 SQL 查詢通常比架一個回傳 XML／JSON（還得解析）的分散式服務 API 容易
想減少執行期依賴時，了解架構中每一層能承擔多少實作是件好事

網路位址型別#

PostgreSQL 內建 cidr、inet、macaddr 三種網路位址型別，同樣附帶索引支援與進階函式、運算子（見 Network Address Types 與 Network Address Functions and Operators 文件章節）。

網路位址的經典資料來源是 web 伺服器日誌。這裡用 Honeynet Project 的 Scan 34 樣本，把 Apache access log 清洗成 CSV 後載入：

create table access_log
  (
    ip      inet,
    ts      timestamptz,
    request text,
    status  integer
  );

\copy access_log from 'access.csv' with csv delimiter ';'

有了 inet 型別，就能分析日誌中的 /24 網段。set_masklen() 函式可以把 IP 位址轉成任意的 CIDR 網路位址：

select distinct on (ip)
       ip,
       set_masklen(ip, 24) as inet_24,
       set_masklen(ip::cidr, 24) as cidr_24
  from access_log
 limit 10;

      ip       │     inet_24      │     cidr_24
═══════════════╪══════════════════╪═════════════════
 4.35.221.243  │ 4.35.221.243/24  │ 4.35.221.0/24
 4.152.207.126 │ 4.152.207.126/24 │ 4.152.207.0/24
 ...

保持 inet 型別時，得到的是完整 IP 加上 /24 標記；要得到 .0/24 的網段表示法必須轉成 cidr。
當然也可以分析 /27、/28 等其他網段——set_masklen(ip::cidr, 27) 會替你算出正確的網段起始位址。用了正確的資料型別，就該拿它做進階處理。

接著就能以任意 CIDR 網段定義分析 access log：

  select set_masklen(ip::cidr, 24) as network,
         count(*) as requests,
         array_length(array_agg(distinct ip), 1) as ipcount
    from access_log
group by network
  having array_length(array_agg(distinct ip), 1) > 1
order by requests desc, ipcount desc;

     network      │ requests │ ipcount
══════════════════╪══════════╪═════════
 4.152.207.0/24   │      140 │       2
 222.95.35.0/24   │       59 │       2
 211.59.0.0/24    │       32 │       2
 61.10.7.0/24     │       25 │      25
 222.166.160.0/24 │       25 │      24
 ...
(12 rows)

範圍型別#

範圍型別（range types）是 PostgreSQL 獨有的功能：在單一欄位裡管理兩個維度的資料，並支援進階處理。主要例子是 daterange——把範圍的下界與上界存成單一值，讓 PostgreSQL 能實作並行安全的範圍重疊檢查（完整資訊見 Range Types 與 Range Functions and Operators 文件章節）。

國際貨幣基金（IMF）按月發布多種貨幣的匯率檔案。一筆匯率從發布起生效、直到下一筆發布為止——正是範圍型別的好用例。以下是本書所用 ELT 腳本的主要部分：

begin;

create schema if not exists raw;

-- 需要超級使用者權限
-- create extension if not exists btree_gist;

create table raw.rates
  (
    currency text,
    date     date,
    rate     numeric
  );

\copy raw.rates from 'rates.csv' with csv delimiter ';'

create table rates
  (
    currency text,
    validity daterange,
    rate     numeric,

    exclude using gist (currency with =,
                        validity with &&)
  );

insert into rates(currency, validity, rate)
     select currency,
            daterange(date,
                      lead(date) over(partition by currency
                                          order by date),
                      '[)'
                     )
            as validity,
            rate
       from raw.rates
   order by date;

commit;

rates 表以**排除約束（exclusion constraint）**保證同一貨幣的生效期間不重疊。exclude using gist (currency with =, validity with &&) 讀作：排除任何「currency 與既有資料相等（=）且 validity 與既有資料重疊（&&）」的元組。這個約束由 GiST 索引實作。
GiST 預設不支援本該由 B-tree 涵蓋的一維型別；為了在排除約束中同時使用一維的 currency，要安裝 contrib 套件中的 btree_gist 擴充。
填資料的查詢用 lead() 視窗函式，把「生效至下一筆發布為止」這句英文規格直接翻成 SQL。

看看歐元的資料，validity 的標準輸出是含下界、不含上界的半開區間：

  select currency, validity, rate
    from rates
   where currency = 'Euro'
order by validity
   limit 10;

 currency │        validity         │   rate
══════════╪═════════════════════════╪══════════
 Euro     │ [2017-05-02,2017-05-03) │ 1.254600
 Euro     │ [2017-05-03,2017-05-04) │ 1.254030
 Euro     │ [2017-05-04,2017-05-05) │ 1.252780
 Euro     │ [2017-05-05,2017-05-08) │ 1.250510
 ...
(10 rows)

有了排除約束，我們確知任一時間點至多只有一筆匯率，應用程式要查特定日期的匯率只要：

select rate
  from rates
 where currency = 'Euro'
   and validity @> date '2017-05-18';

   rate
══════════
 1.240740
(1 row)

@> 運算子讀作「包含（contains）」，PostgreSQL 會利用排除約束的索引高效解決這個查詢。