PostgreSQL 空值 (NULL) 參與運算陷阱與 COALESCE 最佳實踐

PostgreSQL 空值 (NULL) 參與運算陷阱與 COALESCE 最佳實踐

核心陷阱：NULL 與任何值運算結果均為 NULL

在 PostgreSQL (及所有符合 SQL 標準的數據庫) 中，NULL 代表「未知值」。任何算術運算 (+, -, *, /)、比較運算 (=, <, >)、字符串拼接 (||) 涉及 NULL，結果一律為 NULL。這導致報表統計、計算字段頻繁出現「莫名其妙的空值」。

實戰演示

-- 1. 建表
CREATE TABLE test_null (
    id INT,
    num1 INT,
    num2 INT
);

-- 2. 插入含 NULL 數據
INSERT INTO test_null VALUES
(1, 100, 100),
(2, 200, 200),
(3, NULL, 100),  -- num1 為空
(4, 300, NULL),  -- num2 為空
(5, NULL, 500);  -- 雙空

-- 3. 直接相加 → 含 NULL 行結果全成 NULL
SELECT id, num1, num2, num1 + num2 AS sum_raw FROM test_null;
-- 結果:
-- id | num1 | num2 | sum_raw
-- 1  | 100  | 100  | 200
-- 2  | 200  | 200  | 400
-- 3  |      | 100  |        ← NULL!
-- 4  | 300  |      |        ← NULL!
-- 5  |      | 500  |        ← NULL!

解決神器：COALESCE 函數

COALESCE(value, default_value)：返回參數列表中**第一個非 NULL 的值**。若所有參數均為 NULL，返回 NULL。專為「NULL 替換默認值」設計，語法簡潔、可讀性強、標準 SQL 支援。

-- 使用 COALESCE 將 NULL 視為 0 參與運算
SELECT id,
       num1,
       num2,
       COALESCE(num1, 0) + COALESCE(num2, 0) AS sum_fixed
FROM test_null;
-- 結果:
-- id | num1 | num2 | sum_fixed
-- 1  | 100  | 100  | 200
-- 2  | 200  | 200  | 400
-- 3  |      | 100  | 100   ← 0 + 100
-- 4  | 300  |      | 300   ← 300 + 0
-- 5  |      | 500  | 500   ← 0 + 500

進階場景與默認值選擇指南

COALESCE 多參數鏈式用法

優先級遞減：COALESCE(col1, col2, col3, 'default')。常用於「多備選字段取值」：

-- 取用戶顯示名：優先 nickname，次 real_name，再 username，最後 '匿名用戶'
SELECT COALESCE(nickname, real_name, username, '匿名用戶') AS display_name FROM users;

對比：CASE WHEN / NULLIF / IFNULL (非標準)

• CASE WHEN col IS NULL THEN 0 ELSE col END：功能等價但冗長，可讀性較差。
• NULLIF(col, 0)：反向操作，若 col = 0 則返回 NULL (用於避免除零錯誤：num / NULLIF(denom, 0))。
• IFNULL / NVL / ISNULL：非標準，MySQL/Oracle/SQL Server 專用，**避免在 PostgreSQL 使用**，影響遷移兼容性。

性能提示

• COALESCE 內部實現為 CASE 表達式，**極輕量**，無額外函數調用開銷。
• 索引列使用 COALESCE(col, 0) = 100 **會導致索引失效** (函數列)。需查詢非 NULL 值時用 col = 100，需包含 NULL 時用 col = 100 OR col IS NULL 或建立部分索引 CREATE INDEX ON tbl (col) WHERE col IS NOT NULL。

記住：**數據庫運算中遇到 NULL，第一反應想 COALESCE**，用業務語義正確的默認值填補未知，讓報表數據「不再失聯」！