資料型別完全指南：PostgreSQL 豐富的型別系統 | PostgreSQL
2026/06/18
PostgreSQL 擁有業界最完整的原生型別集合——從基礎的整數、字串、日期時間，到進階的 JSONB、Range、Array、Enum、Domain 與網路位址型別，其型別系統建立在
pg_type系統目錄之上，具備高度可擴展性。選對型別，不僅影響資料正確性，更直接關係到儲存效率與查詢效能。
型別系統架構
PostgreSQL 的每個型別都是系統目錄 pg_type 中的一筆紀錄，包含 OID、輸入/輸出函式、儲存策略等元資訊。型別在 PostgreSQL 中是真正的「一等公民」：
pg_type 核心欄位：
├── typname — 型別名稱
├── typoid — 唯一 OID 識別碼
├── typlen — 固定長度（-1 表示可變長度）
├── typbyval — 是否以傳值方式處理
├── typcategory — 型別分類代碼
├── typinput — 文字輸入函式
├── typoutput — 文字輸出函式
└── typstorage — TOAST 儲存策略
-- 查詢所有型別分類
SELECT typcategory, typname, typlen
FROM pg_type
WHERE typisdefined = true
ORDER BY typcategory, typname
LIMIT 20;
型別分類總覽
PostgreSQL 型別分類體系
│
├── 數值型別（Numeric）
│ ├── 整數：smallint / integer / bigint
│ ├── 自動遞增：GENERATED ALWAYS AS IDENTITY（PG10+ 推薦）
│ ├── 精確小數：numeric / decimal
│ └── 浮點：real / double precision
│
├── 字串型別（String）
│ ├── text（無限制長度，推薦使用）
│ ├── varchar(n)（有長度限制）
│ └── char(n)（固定長度，右側填充空格）
│
├── 日期時間型別（Date/Time）
│ ├── timestamptz（含時區，推薦使用）
│ ├── timestamp（無時區）
│ ├── date / time / interval
│ └── timetz（不建議使用）
│
├── 布林 / 二進位
│ ├── boolean
│ └── bytea
│
├── JSON 型別
│ ├── json（文字儲存，每次解析）
│ └── jsonb（二進位儲存，支援索引，推薦）
│
├── 網路型別：inet / cidr / macaddr
├── 範圍型別：int4range / tstzrange / daterange ...
├── 陣列型別：任何型別加 [] 即為陣列
├── UUID：128 位元全域唯一識別碼
├── Enum / Domain / Composite（自訂型別）
└── 全文搜尋：tsvector / tsquery
數值型別
整數型別選擇
| 型別 | 別名 | 大小 | 範圍 |
|---|---|---|---|
smallint | int2 | 2 bytes | -32,768 ~ 32,767 |
integer | int4 | 4 bytes | -2,147,483,648 ~ 2,147,483,647 |
bigint | int8 | 8 bytes | ±9.2×10^18 |
-- smallint：適合有限範圍的值
CREATE TABLE calendar_event (
day_of_week smallint CHECK (day_of_week BETWEEN 0 AND 6)
);
-- integer：最常用的整數型別
CREATE TABLE orders (
order_id integer PRIMARY KEY,
quantity integer NOT NULL
);
-- bigint：需要超大範圍時使用
CREATE TABLE event_log (
event_id bigint PRIMARY KEY,
user_id bigint NOT NULL
);
serial vs GENERATED AS IDENTITY
serial 是 PG10 之前的慣例，實際上是 integer DEFAULT nextval('seq') 的語法糖。PG10+ 推薦使用 GENERATED ALWAYS AS IDENTITY：
-- 舊式 serial（仍可用，但不推薦新程式碼）
CREATE TABLE old_style (
id serial PRIMARY KEY
);
-- PG10+ 推薦：GENERATED ALWAYS AS IDENTITY
CREATE TABLE new_style (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text
);
-- ALWAYS 模式不允許直接 INSERT 指定 id 值
-- GENERATED BY DEFAULT AS IDENTITY：允許覆寫（較寬鬆）
CREATE TABLE flexible_id (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name text
);
差異：serial 的序列是獨立物件，刪除表時不會自動刪除（需 CASCADE）；IDENTITY 欄位的序列隸屬於欄位，自動清理。
numeric 精確度
-- numeric(precision, scale)
-- precision：總位數 scale：小數點後位數
CREATE TABLE financial (
price numeric(12, 2), -- 最大 999,999,999,999.99
tax_rate numeric(5, 4) -- 如 0.1500（15%）
);
-- 常見錯誤：用 float 儲存金融資料
SELECT 0.1::float + 0.2::float; -- 0.30000000000000004（浮點誤差！）
SELECT 0.1::numeric + 0.2::numeric; -- 0.3（精確）
規則：涉及金錢或需要精確計算的場景，一律使用 numeric，絕不用 float。
字串型別
text vs varchar vs char
在 PostgreSQL 中，text 和 varchar 底層儲存機制完全相同，varchar(n) 只是在 text 上增加了長度限制。除非業務上有明確的長度需求，否則直接用 text：
-- 推薦：使用 text
CREATE TABLE users (
username text NOT NULL,
bio text,
CONSTRAINT username_length CHECK (length(username) BETWEEN 3 AND 50)
);
-- varchar(n)：等同於 text 加上長度 CHECK
CREATE TABLE products (
sku varchar(20) NOT NULL -- 業務上確定不超過 20 字元
);
-- char(n)：固定長度，右側填充空格，幾乎沒有使用場景
CREATE TABLE country_code (
code char(2) -- 如 'TW', 'US'
);
-- 陷阱：'TW'::char(5) = 'TW'::char(2) 為 true（比較時忽略尾部空格）
Collation（排序規則）
-- 資料庫層級 collation
CREATE DATABASE mydb
WITH ENCODING 'UTF8'
LC_COLLATE 'zh_TW.UTF-8'
LC_CTYPE 'zh_TW.UTF-8';
-- 欄位層級 collation
CREATE TABLE articles (
title text COLLATE "zh-TW-x-icu", -- ICU 排序（PG10+）
slug text COLLATE "C" -- 位元組排序，效能最佳
);
-- 查詢可用 collation
SELECT collname FROM pg_collation
WHERE collencoding = -1 OR collencoding = 6;
日期時間型別
timestamp vs timestamptz
這是 PostgreSQL 中最重要的設計決策之一。timestamp 儲存「壁鐘時間」字串，不知道時區；timestamptz 在儲存時換算為 UTC，讀取時依 TimeZone 設定轉回。
永遠使用 timestamptz，除非你有非常明確的理由不用。
CREATE TABLE events (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
event_name text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
scheduled_at timestamptz
);
-- timestamptz 的行為
SET TimeZone = 'Asia/Taipei';
INSERT INTO events (event_name, scheduled_at)
VALUES ('會議', '2026-05-23 14:00:00');
-- 儲存時：14:00 Asia/Taipei → 06:00 UTC
SET TimeZone = 'UTC';
SELECT scheduled_at FROM events WHERE event_name = '會議';
-- 顯示 2026-05-23 06:00:00+00
SET TimeZone = 'America/New_York';
SELECT scheduled_at FROM events WHERE event_name = '會議';
-- 顯示 2026-05-23 02:00:00-04
日期時間操作
-- 取得當前時間
SELECT now(); -- 當前 Transaction 開始時間
SELECT clock_timestamp(); -- 系統時間（每次呼叫都更新）
SELECT current_date; -- 當前日期
SELECT current_time; -- 當前時間
-- interval 運算
SELECT now() + interval '1 day';
SELECT now() + interval '1 year 2 months 3 days';
SELECT '2026-05-23'::date - '2026-01-01'::date; -- 天數差（integer）
-- 日期截斷
SELECT date_trunc('month', now()); -- 截斷到月份開頭
SELECT date_trunc('week', now()); -- 截斷到週一
-- 日期部分提取
SELECT extract(year FROM now()); -- 年份
SELECT extract(dow FROM now()); -- 星期幾（0=週日）
SELECT extract(epoch FROM now()); -- Unix timestamp（秒）
-- 時區轉換
SELECT now() AT TIME ZONE 'Asia/Taipei';
SELECT now() AT TIME ZONE 'UTC';
UUID
-- PG13+ 內建 gen_random_uuid()（推薦）
CREATE TABLE sessions (
session_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
user_id bigint NOT NULL,
created_at timestamptz DEFAULT now()
);
SELECT gen_random_uuid();
-- 如：a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
UUID vs BIGINT 作為主鍵：
| 比較項目 | UUID | BIGINT |
|---|---|---|
| 儲存大小 | 16 bytes | 8 bytes |
| 分散式系統 | 無需協調即可生成 | 需要序列同步 |
| 安全性 | 不可猜測 | 可猜測 |
| 索引效率 | 略低（隨機分散） | 較高（遞增有序） |
| 可讀性 | 差 | 好 |
網路型別
CREATE TABLE network_config (
device_name text,
ip_address inet, -- IPv4/IPv6 位址
network cidr, -- 網路位址
mac macaddr -- MAC 位址
);
INSERT INTO network_config VALUES
('router01', '192.168.1.1', '192.168.1.0/24', '08:00:2b:01:02:03');
-- 網路運算子
SELECT '192.168.1.100'::inet << '192.168.1.0/24'::cidr; -- 是否在子網中
SELECT network('192.168.1.100/24'); -- 取得網路位址
SELECT broadcast('192.168.1.0/24'); -- 廣播位址
-- GiST 索引支援快速範圍查詢
CREATE INDEX idx_network ON network_config USING gist (ip_address inet_ops);
範圍型別（Range）
PostgreSQL 9.2+ 引入的範圍型別，可表達連續的值區間，搭配排他約束（EXCLUDE） 能優雅地解決「不可重疊」的業務邏輯：
CREATE TABLE reservations (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
room_number integer NOT NULL,
reserved_period tstzrange NOT NULL,
-- 排他約束：同一房間的預約不能重疊
EXCLUDE USING gist (room_number WITH =, reserved_period WITH &&)
);
INSERT INTO reservations (room_number, reserved_period) VALUES
(101, '[2026-06-01 14:00, 2026-06-03 12:00)');
-- 範圍運算子
SELECT '[1,5]'::int4range @> 3; -- 包含值 3（true）
SELECT '[1,5]'::int4range && '[4,8]'::int4range; -- 是否重疊（true）
SELECT '[1,5]'::int4range * '[3,8]'::int4range; -- 交集：[3,5]
SELECT '[1,5]'::int4range + '[3,8]'::int4range; -- 聯集：[1,8]
-- 範圍邊界語法
-- [lower, upper) — 左閉右開（最常用）
-- [lower, upper] — 兩端閉合
-- (lower, upper) — 兩端開放
-- multirange（PG14+）
SELECT '{[1,3], [5,8]}'::int4multirange; -- 多個不連續範圍
陣列型別（Array）
CREATE TABLE blog_posts (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL,
tags text[]
);
-- 插入陣列值
INSERT INTO blog_posts (title, tags)
VALUES ('PostgreSQL 教學', ARRAY['postgresql', 'database', 'sql']);
-- 陣列存取（1-based 索引）
SELECT tags[1] FROM blog_posts; -- 第一個元素
SELECT tags[1:2] FROM blog_posts; -- 前兩個元素（切片）
SELECT array_length(tags, 1) FROM blog_posts; -- 第一維長度
-- 陣列操作
SELECT ARRAY['a', 'b'] || ARRAY['c', 'd']; -- 連接：{a,b,c,d}
SELECT array_append(ARRAY['a', 'b'], 'c'); -- 附加：{a,b,c}
SELECT unnest(ARRAY['a', 'b', 'c']); -- 展開為多列
-- 陣列查詢運算子
SELECT * FROM blog_posts WHERE 'postgresql' = ANY(tags); -- 包含元素
SELECT * FROM blog_posts WHERE tags @> ARRAY['sql']; -- 包含子集
SELECT * FROM blog_posts WHERE tags && ARRAY['python', 'sql']; -- 有交集
-- GIN 索引加速陣列查詢
CREATE INDEX idx_tags ON blog_posts USING gin (tags);
Enum 型別
-- 建立 Enum 型別
CREATE TYPE order_status AS ENUM (
'pending', 'confirmed', 'shipped', 'delivered', 'cancelled'
);
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
status order_status NOT NULL DEFAULT 'pending'
);
-- Enum 有隱含順序（按定義順序）
SELECT * FROM orders WHERE status > 'pending';
SELECT * FROM orders ORDER BY status;
-- 新增 Enum 值（PG9.1+）
ALTER TYPE order_status ADD VALUE 'processing' AFTER 'confirmed';
-- 注意：ADD VALUE 在 Transaction 中無法回滾！
-- 不支援刪除 Enum 值（需要重建型別）
Enum vs text + CHECK：
| 比較項目 | Enum | text + CHECK |
|---|---|---|
| 儲存效率 | 高（4 bytes OID） | 可變長度 |
| 排序語義 | 有（按定義順序） | 無（字母序） |
| 修改靈活性 | 低（ADD 有限制） | 高（ALTER CHECK） |
| 跨 DB 遷移 | 較困難 | 容易 |
-- 替代方案：text + CHECK（更靈活）
CREATE TABLE flexible_orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
status text NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered'))
);
Domain 型別
Domain 是在既有型別上加上 CHECK 約束的衍生型別，實現「型別即文件」：
CREATE DOMAIN positive_integer AS integer
CHECK (VALUE > 0);
CREATE DOMAIN email_address AS text
CHECK (VALUE ~ '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$');
CREATE DOMAIN percentage AS numeric(5, 2)
CHECK (VALUE >= 0 AND VALUE <= 100);
CREATE TABLE employees (
id positive_integer PRIMARY KEY,
email email_address NOT NULL UNIQUE,
commission_rate percentage DEFAULT 0
);
INSERT INTO employees VALUES (1, 'alice@example.com', 15.50); -- 成功
INSERT INTO employees VALUES (-1, 'bob@example.com', 0); -- 報錯：positive_integer
INSERT INTO employees VALUES (2, 'invalid-email', 0); -- 報錯：email_address
INSERT INTO employees VALUES (3, 'charlie@example.com', 150); -- 報錯：percentage
Domain 的優勢在於：多個表格可重用相同的驗證邏輯，修改一處即可全域生效。
型別轉換機制
PostgreSQL 的型別轉換分為三種層次：
-- 1. 顯式轉換（Explicit Cast）
SELECT CAST('2026-01-01' AS date); -- SQL 標準語法
SELECT '2026-01-01'::date; -- PostgreSQL 簡寫
SELECT '{"key":"val"}'::jsonb;
-- 2. 賦值轉換（Assignment Cast）
-- INSERT/UPDATE 時自動嘗試轉換
CREATE TABLE demo (val integer);
INSERT INTO demo VALUES ('42'); -- 字串 '42' 自動轉為 integer
INSERT INTO demo VALUES ('abc'); -- 無法轉換，報錯
-- 3. 隱式轉換（Implicit Cast）
-- 在表達式中自動發生，PostgreSQL 非常保守
SELECT 1 + 1.5; -- integer → numeric，結果為 2.5
-- 查詢型別轉換路徑
SELECT castsource::regtype, casttarget::regtype, castcontext
FROM pg_cast
WHERE castsource = 'integer'::regtype
ORDER BY casttarget::regtype::text;
-- castcontext: 'e'=explicit, 'a'=assignment, 'i'=implicit
型別對索引效能的影響
-- 型別不匹配會導致隱式轉換，使索引失效
CREATE TABLE accounts (
user_id varchar(20) PRIMARY KEY,
balance numeric
);
EXPLAIN SELECT * FROM accounts WHERE user_id = 12345; -- 索引可能失效！
EXPLAIN SELECT * FROM accounts WHERE user_id = '12345'; -- 正確使用索引
各型別儲存大小比較：
| 型別 | 固定/可變 | 大小 | 對齊 |
|---|---|---|---|
| boolean | 固定 | 1 byte | 1 byte |
| smallint | 固定 | 2 bytes | 2 bytes |
| integer | 固定 | 4 bytes | 4 bytes |
| bigint | 固定 | 8 bytes | 8 bytes |
| date | 固定 | 4 bytes | 4 bytes |
| timestamptz | 固定 | 8 bytes | 8 bytes |
| uuid | 固定 | 16 bytes | 4 bytes |
| numeric | 可變 | 2+ bytes | 4 bytes |
| text/varchar | 可變 | 4+len bytes | 4 bytes |
| jsonb | 可變 | 4+len bytes | 4 bytes |
B-tree 索引對固定長度型別效率最高。text/varchar 索引受限於 2704 bytes（B-tree 頁面的 1/4），超過時需使用前綴索引或 hash 索引。
常見陷阱
浮點精確度問題
SELECT 0.1::float + 0.2::float = 0.3::float; -- false！
SELECT 0.1::float + 0.2::float; -- 0.30000000000000004
-- 金融計算必須使用 numeric
SELECT 0.1::numeric + 0.2::numeric = 0.3::numeric; -- true
timestamp 與 timestamptz 混用
-- 絕對不要在同一表格混用兩者
-- 錯誤設計
CREATE TABLE bad_design (
created_at timestamp, -- 無時區
updated_at timestamptz -- 有時區
);
-- 比較兩欄位時會依 session TimeZone 隱式轉換，極易出錯
-- 正確設計
CREATE TABLE good_design (
created_at timestamptz DEFAULT now(),
updated_at timestamptz DEFAULT now()
);
char(n) 的空格填充
SELECT 'TW'::char(5) = 'TW'::char(2); -- true（忽略尾部空格）
SELECT 'TW'::text = 'TW '::text; -- false（text 不忽略）
-- 結論：除非有特殊需求，不要使用 char(n)
版本演進
| 版本 | 型別改進 |
|---|---|
| PG9.2 | 範圍型別（Range Types） |
| PG9.4 | jsonb 型別 |
| PG10 | GENERATED AS IDENTITY 欄位 |
| PG13 | gen_random_uuid() 內建（無需擴充） |
| PG14 | multirange 型別 |
總結
資料型別 是 PostgreSQL 型別系統的核心基礎：
- 數值：金融計算用
numeric，絕不用float；自動遞增用GENERATED AS IDENTITY - 字串：優先使用
text，varchar(n)僅在有明確長度限制時使用，避免char(n) - 日期時間：永遠使用
timestamptz，除非有非常明確的理由 - UUID：PG13+ 用
gen_random_uuid()，適合分散式系統的主鍵 - 範圍型別：搭配 EXCLUDE 約束解決「不可重疊」的業務邏輯
- 陣列與 JSONB：搭配 GIN 索引實現高效查詢
- Enum vs text + CHECK：需要排序語義用 Enum，需要靈活性用 text + CHECK
- Domain：在多表重用驗證邏輯的最佳方式
- 型別匹配：保持應用端型別與資料庫欄位型別一致，避免隱式轉換使索引失效
下一篇，我們將深入探討 DDL 與 Schema 管理——如何用 PostgreSQL 的豐富語法設計與維護資料庫結構。