PostgreSQL 支援相當豐富的資料型態。以下是常見類型與使用情境。 ## 1. 整數與數值型態 | 型態 | 說明 | 範例 | |---|---|---| | `smallint` | 2 位元組整數，約 -32,768～32,767 | 年齡、數量 | | `integer` / `int` | 4 位元組整數，最常用 | 編號、計數 | | `bigint` | 8 位元組整數，適合很大的數值 | 大型流水號、交易金額分（最小貨幣單位） | | `numeric(p,s)` / `decimal(p,s)` | 精確小數，適合金額與財務資料 | `numeric(10,2)` | | `real` | 4 位元組浮點數 | 科學計算 | | `double precision` | 8 位元組浮點數 | 高精度近似計算 | ```sql price numeric(10, 2); quantity integer; ``` ### `serial` 與 `identity` 過去常用： ```sql id serial primary key ``` `serial` 實際上是自動建立 sequence 的方便語法。較新的 PostgreSQL 建議使用 SQL 標準的 identity： ```sql id integer generated always as identity primary key ``` 也可以使用： ```sql id bigint generated by default as identity ``` --- ## 2. 字串型態 | 型態 | 說明 | |---|---| | `char(n)` | 固定長度字串，不足部分會補空白 | | `varchar(n)` | 可變長度字串，最多 `n` 個字元 | | `text` | 不限制明確長度的文字，PostgreSQL 中很常用 | ```sql username varchar(50); description text; country_code char(2); ``` 通常若沒有特別需要限制長度，可直接使用 `text`。`varchar(n)` 的長度限制只有在資料驗證有明確需求時才有意義。 --- ## 3. 日期與時間型態 | 型態 | 說明 | |---|---| | `date` | 只有日期 | | `time` | 只有時間 | | `timestamp` | 日期與時間，不含時區 | | `timestamptz` | 日期與時間，含時區語意 | | `interval` | 時間間隔 | ```sql birth_date date; created_at timestamptz; duration interval; ``` 一般建議： - 儲存事件發生時間：使用 `timestamptz` - 儲存生日、節日等純日期：使用 `date` - 不要只用 `timestamp` 儲存需要跨時區處理的時間 例如： ```sql created_at timestamptz default now() ``` --- ## 4. 布林型態 `boolean` 可儲存： - `TRUE` - `FALSE` - `NULL` ```sql is_active boolean default true; ``` 輸入時也可使用： ```sql true false 't' 'f' ``` --- ## 5. UUID `uuid` 用於儲存通用唯一識別碼，適合分散式系統或不希望使用連續整數 ID 的情境。 ```sql user_id uuid primary key; ``` 搭配 UUID 產生函式，例如： ```sql CREATE EXTENSION IF NOT EXISTS pgcrypto; id uuid DEFAULT gen_random_uuid() ``` --- ## 6. JSON 與 JSONB | 型態 | 說明 | |---|---| | `json` | 保留原始 JSON 文字格式 | | `jsonb` | 以二進位格式儲存，可更有效率查詢與建立索引 | ```sql profile jsonb; ``` 範例資料： ```json { "name": "Amy", "age": 30, "tags": ["admin", "user"] } ``` 通常建議優先使用 `jsonb`： ```sql SELECT profile->>'name' FROM users WHERE profile @> '{"role": "admin"}'; ``` 適合儲存結構彈性較高的資料，但不應完全取代設計良好的關聯式欄位。 --- ## 7. 陣列型態 PostgreSQL 支援欄位儲存陣列： ```sql tags text[]; scores integer[]; ``` 建立資料： ```sql INSERT INTO articles (tags) VALUES (ARRAY['postgresql', 'database']); ``` 查詢： ```sql SELECT * FROM articles WHERE 'postgresql' = ANY(tags); ``` 陣列適合簡單的一對多資料，但若資料需要獨立查詢、關聯或大量管理，通常建立另一張關聯表會更合適。 --- ## 8. 二進位資料 `bytea` 用於儲存二進位資料，例如雜湊值、加密資料或小型檔案。 ```sql file_data bytea; ``` 大型檔案通常不建議直接放在一般資料欄位中，可考慮使用物件儲存服務，資料庫只保存檔案位置與相關資訊。 --- ## 9. 列舉型態 `enum` 可定義固定的一組值： ```sql CREATE TYPE order_status AS ENUM ('pending', 'paid', 'cancelled'); CREATE TABLE orders ( status order_status ); ``` 優點是值受到限制；缺點是日後新增或修改選項較不彈性。若狀態會經常變動，通常可使用查詢表或 `text` 加上 `CHECK` 約束。 --- ## 10. 網路位址型態 | 型態 | 說明 | |---|---| | `inet` | IPv4 或 IPv6 位址 | | `cidr` | IPv4 或 IPv6 網路區段 | | `macaddr` | MAC 位址 | ```sql ip_address inet; network cidr; ``` 例如： ```sql SELECT '192.168.1.10'::inet << '192.168.1.0/24'::cidr; ``` --- ## 11. 範圍型態 PostgreSQL 支援表示範圍的型態，例如： - `int4range` - `int8range` - `numrange` - `tsrange` - `tstzrange` - `daterange` ```sql valid_period tstzrange; ``` 適合預約期間、價格有效區間或日期範圍等資料。 --- ## 12. 幾何與地理資料 內建幾何型態包括： - `point` - `line` - `polygon` - `circle` 若需要完整 GIS 功能，通常使用 PostgreSQL 的 **PostGIS** 擴充套件，例如： - `geometry` - `geography` --- ## 13. `NULL` 與資料型態的關係 任何欄位都可能是 `NULL`，表示「未知」或「沒有值」，但不等同於： - 空字串 `''` - 數字 `0` - `false` 例如： ```sql email text NULL; ``` 若欄位不可為空，應加上： ```sql email text NOT NULL; ``` --- ## 常見選擇建議 - 一般整數：`integer` - 超大型整數：`bigint` - 金額：`numeric(precision, scale)` - 一般文字：`text` - 有時區的時間：`timestamptz` - 日期：`date` - 開關狀態：`boolean` - 分散式唯一 ID：`uuid` - 彈性結構資料：`jsonb` - 自動遞增主鍵：`identity` - IP 位址：`inet` 選擇資料型態時，應考慮資料的實際語意、精確度、大小、查詢需求，以及是否需要索引與約束。