資料型別完全指南

選擇正確的資料型別是資料庫設計的基礎。PostgreSQL 提供了豐富的內建型別，甚至允許你自訂型別。本篇將完整介紹各種型別的特性與使用時機。

一、數值型別

整數型別

型別	儲存空間	範圍	用途
`SMALLINT`	2 bytes	-32,768 ~ 32,767	狀態碼、年齡
`INTEGER`	4 bytes	±21 億	最常用
`BIGINT`	8 bytes	±922 京	大型計數器、ID

自動遞增

sql

-- 傳統寫法
id SERIAL PRIMARY KEY         -- 等同於 INTEGER + 自動遞增

-- 現代寫法（PostgreSQL 10+，推薦）
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY

IDENTITY 欄位比 SERIAL 更符合 SQL 標準，且可以防止手動插入值。

精確小數

sql

-- NUMERIC(精度, 小數位數)
price NUMERIC(10, 2)   -- 最多 10 位數，小數 2 位

-- 範例
INSERT INTO products (price) VALUES (9999999.99);  -- OK
INSERT INTO products (price) VALUES (99999999.99); -- 錯誤：超過精度

> **金額請用 NUMERIC，不要用 FLOAT** > `FLOAT` 和 `DOUBLE PRECISION` 是浮點數，會有精度誤差。處理金錢時一定要用 `NUMERIC`。

浮點數

sql

-- 避免用於金額，適合科學計算
temperature REAL              -- 4 bytes，約 6 位精度
measurement DOUBLE PRECISION  -- 8 bytes，約 15 位精度

二、字串型別

型別	特性	用途
`VARCHAR(n)`	變動長度，最多 n 字元	有明確上限的欄位（如 email）
`CHAR(n)`	固定長度，不足補空白	固定長度代碼（如國碼 `TW`）
`TEXT`	無限長度	文章內容、描述

效能迷思

> **VARCHAR(255) 的神話**

在 PostgreSQL 中，VARCHAR(255) 和 TEXT 效能完全相同。長度限制只是邏輯約束。如果不確定長度，直接用 TEXT 即可。

sql

-- 這三者在 PostgreSQL 中效能相同
name VARCHAR(100)
name VARCHAR(255)
name TEXT

三、日期與時間型別

型別	儲存內容	範例
`DATE`	只有日期	`2024-01-15`
`TIME`	只有時間	`14:30:00`
`TIMESTAMP`	日期 + 時間（無時區）	`2024-01-15 14:30:00`
`TIMESTAMPTZ`	日期 + 時間（含時區）	`2024-01-15 14:30:00+08`
`INTERVAL`	時間間隔	`2 days 3 hours`

時區處理

sql

-- 建議永遠使用 TIMESTAMPTZ
created_at TIMESTAMPTZ DEFAULT NOW()

-- 查詢時會自動轉換成客戶端時區
SET TIMEZONE = 'Asia/Taipei';
SELECT created_at FROM events;

> **生產環境一律使用 TIMESTAMPTZ** > `TIMESTAMP` 不含時區資訊，當伺服器時區變更時會造成資料混亂。

INTERVAL 實用技巧

sql

-- 計算 30 天前
SELECT NOW() - INTERVAL '30 days';

-- 計算到期日
SELECT order_date + INTERVAL '7 days' AS due_date FROM orders;

-- 格式化間隔
SELECT age(NOW(), birth_date) FROM users;
-- 結果：25 years 3 mons 15 days

四、布林與 UUID

布林值

sql

is_active BOOLEAN DEFAULT TRUE

-- PostgreSQL 接受多種表示法
INSERT INTO users (is_active) VALUES (true);
INSERT INTO users (is_active) VALUES ('yes');
INSERT INTO users (is_active) VALUES (1);      -- 錯誤！不接受數字

UUID

UUID（通用唯一識別碼）適合作為分散式系統的主鍵：

sql

-- 啟用擴充
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- 使用 UUID 作為主鍵
CREATE TABLE users (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    name TEXT
);

> **UUID v4 vs v7**

v4：完全隨機，索引效能較差
v7（PostgreSQL 17+）：含時間戳，對 B-Tree 索引更友善

五、 JSON 與 JSONB

PostgreSQL 提供兩種 JSON 型別：

型別	儲存方式	特點
`JSON`	純文字	保留格式，無法建索引
`JSONB`	二進位	可建索引，查詢快，推薦使用

基本操作

sql

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    attributes JSONB
);

INSERT INTO products (name, attributes) VALUES
    ('iPhone', '{"color": "black", "storage": 256, "5g": true}');

-- 取得單一欄位（返回 JSON）
SELECT attributes->'color' FROM products;
-- 結果："black"

-- 取得單一欄位（返回文字）
SELECT attributes->>'color' FROM products;
-- 結果：black

-- 取得巢狀欄位
SELECT attributes->'specs'->>'cpu' FROM products;

JSONB 索引

sql

-- GIN 索引，支援 @> 包含查詢
CREATE INDEX idx_attributes ON products USING GIN (attributes);

-- 查詢包含特定屬性的產品
SELECT * FROM products WHERE attributes @> '{"color": "black"}';

JSONB 更新

sql

-- 新增或修改欄位
UPDATE products
SET attributes = attributes || '{"warranty": true}'
WHERE id = 1;

-- 刪除欄位
UPDATE products
SET attributes = attributes - 'warranty'
WHERE id = 1;

-- 修改巢狀欄位
UPDATE products
SET attributes = jsonb_set(attributes, '{specs,cpu}', '"M3"')
WHERE id = 1;

六、陣列型別

PostgreSQL 原生支援陣列，非常方便：

sql

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title TEXT,
    tags TEXT[]  -- 文字陣列
);

INSERT INTO posts (title, tags) VALUES
    ('PostgreSQL 入門', ARRAY['database', 'postgresql', 'sql']);

-- 查詢包含特定標籤的文章
SELECT * FROM posts WHERE 'postgresql' = ANY(tags);

-- 查詢包含所有指定標籤的文章
SELECT * FROM posts WHERE tags @> ARRAY['database', 'sql'];

陣列操作函數

sql

-- 取得陣列長度
SELECT array_length(tags, 1) FROM posts;

-- 取得第一個元素（索引從 1 開始）
SELECT tags[1] FROM posts;

-- 新增元素
UPDATE posts SET tags = array_append(tags, 'tutorial') WHERE id = 1;

-- 移除元素
UPDATE posts SET tags = array_remove(tags, 'sql') WHERE id = 1;

七、自訂型別

ENUM 列舉型別

sql

-- 建立列舉
CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered');

-- 使用列舉
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status order_status DEFAULT 'pending'
);

-- 只能插入定義的值
INSERT INTO orders (status) VALUES ('shipped');  -- OK
INSERT INTO orders (status) VALUES ('cancelled'); -- 錯誤！

> **ENUM 的缺點**

新增值需要 ALTER TYPE，刪除值很麻煩。如果值經常變動，考慮用 VARCHAR + CHECK 約束。

DOMAIN 型別

為既有型別加上約束：

sql

-- 建立 email 型別
CREATE DOMAIN email AS TEXT
CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z]{2,}$');

-- 使用
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email email NOT NULL
);

INSERT INTO users (email) VALUES ('test@example.com');  -- OK
INSERT INTO users (email) VALUES ('invalid-email');     -- 錯誤！

八、型別選擇速查表

需求	推薦型別	避免
主鍵	`SERIAL`、`BIGSERIAL`、`UUID`
金額	`NUMERIC(精度, 小數位)`	`FLOAT`、`REAL`
一般文字	`TEXT`	`VARCHAR(255)`（無意義）
固定長度代碼	`CHAR(n)`
日期時間	`TIMESTAMPTZ`	`TIMESTAMP`（無時區）
結構化資料	`JSONB`	`JSON`
少量固定選項	`ENUM`
多值標籤	`TEXT[]`	用逗號分隔的字串

總結

PostgreSQL 的型別系統非常強大：

精確計算用 NUMERIC
無限文字用 TEXT
時間戳記用 TIMESTAMPTZ
結構化資料用 JSONB
多值欄位用陣列 []

選擇正確的型別，不僅能確保資料完整性，還能提升查詢效能。

進階挑戰

建立一個 products 表，使用 JSONB 儲存產品規格，並為 attributes->>'category' 建立表達式索引
設計一個使用 DOMAIN 型別的電話號碼欄位，確保格式為 +886-XXX-XXX-XXX
比較 UUID v4 和 SERIAL 作為主鍵時的 INSERT 效能差異

延伸閱讀與資源

← 上一章：環境建置 | 返回專題首頁 | 下一章：表設計與約束 →

資料型別完全指南 ​

一、 數值型別 ​

整數型別 ​

自動遞增 ​

精確小數 ​

浮點數 ​

二、 字串型別 ​

效能迷思 ​

三、 日期與時間型別 ​

時區處理 ​

INTERVAL 實用技巧 ​

四、 布林與 UUID ​

布林值 ​

UUID ​

五、 JSON 與 JSONB ​

基本操作 ​

JSONB 索引 ​

JSONB 更新 ​

六、 陣列型別 ​

陣列操作函數 ​

七、 自訂型別 ​

ENUM 列舉型別 ​

DOMAIN 型別 ​

八、 型別選擇速查表 ​

總結 ​

進階挑戰 ​

延伸閱讀與資源 ​