PostgreSQL NULL值

概述

NULL表示"未知"或"无值"，它不等于空字符串或零。在PostgreSQL中正确处理NULL值对于数据完整性和查询准确性非常重要。

NULL的特性

基本特性

• NULL不等于任何值，包括NULL本身
• NULL与任何值的运算结果都是NULL
• NULL在布尔运算中表示"未知"

-- NULL的比较
SELECT NULL = NULL;      -- 结果: NULL（不是TRUE）
SELECT NULL <> NULL;     -- 结果: NULL
SELECT NULL = 0;         -- 结果: NULL
SELECT NULL = '';        -- 结果: NULL

检测NULL值

IS NULL和IS NOT NULL

-- 查找空值
SELECT * FROM users WHERE email IS NULL;

-- 查找非空值
SELECT * FROM users WHERE email IS NOT NULL;

-- 错误写法（永远不会返回结果）
SELECT * FROM users WHERE email = NULL;  -- 不要这样写！

NULL与运算

算术运算

SELECT 10 + NULL;    -- 结果: NULL
SELECT 10 * NULL;    -- 结果: NULL
SELECT 10 / NULL;    -- 结果: NULL
SELECT NULL + NULL;  -- 结果: NULL

字符串连接

SELECT 'Hello' || NULL;  -- 结果: NULL
SELECT CONCAT('Hello', NULL, 'World');  -- 结果: 'HelloWorld'（CONCAT忽略NULL）

布尔运算

SELECT TRUE AND NULL;   -- 结果: NULL
SELECT FALSE AND NULL;  -- 结果: FALSE
SELECT TRUE OR NULL;    -- 结果: TRUE
SELECT FALSE OR NULL;   -- 结果: NULL
SELECT NOT NULL;        -- 结果: NULL

处理NULL的函数

COALESCE函数

返回第一个非NULL值：

-- 基本用法
SELECT COALESCE(NULL, 'default');  -- 结果: 'default'
SELECT COALESCE(NULL, NULL, 'third');  -- 结果: 'third'
SELECT COALESCE('first', NULL, 'third');  -- 结果: 'first'

-- 实际应用
SELECT
    username,
    COALESCE(nickname, username) as display_name
FROM users;

-- 处理可能为NULL的计算
SELECT
    product_name,
    COALESCE(discount_price, original_price) as actual_price
FROM products;

NULLIF函数

如果两个值相等则返回NULL：

-- 基本用法
SELECT NULLIF(10, 10);  -- 结果: NULL
SELECT NULLIF(10, 20);  -- 结果: 10

-- 避免除零错误
SELECT 100 / NULLIF(divisor, 0) FROM calculations;

-- 将空字符串转为NULL
SELECT NULLIF(email, '') FROM users;

NVL函数（通过COALESCE实现）

-- PostgreSQL没有NVL，使用COALESCE替代
SELECT COALESCE(commission, 0) FROM employees;

NULL与聚合函数

聚合函数忽略NULL

-- COUNT(*)计算所有行，COUNT(column)忽略NULL
SELECT COUNT(*) FROM users;         -- 所有行
SELECT COUNT(email) FROM users;     -- email不为NULL的行

-- SUM、AVG等忽略NULL值
SELECT AVG(salary) FROM employees;  -- 只计算非NULL值的平均

-- 示例数据
-- salary: 100, 200, NULL, 300
SELECT AVG(salary);  -- 结果: 200（不是150）

包含NULL的聚合

-- 如果需要将NULL视为0
SELECT AVG(COALESCE(salary, 0)) FROM employees;

-- 统计NULL值数量
SELECT COUNT(*) - COUNT(column_name) as null_count FROM table_name;

NULL与排序

默认排序行为

-- ASC时NULL排在最后
SELECT * FROM products ORDER BY price ASC;

-- DESC时NULL排在最前
SELECT * FROM products ORDER BY price DESC;

控制NULL排序位置

-- NULL排在最前
SELECT * FROM products ORDER BY price NULLS FIRST;

-- NULL排在最后
SELECT * FROM products ORDER BY price DESC NULLS LAST;

NULL与DISTINCT

-- DISTINCT将所有NULL视为相等
SELECT DISTINCT category FROM products;
-- 多个NULL只返回一个NULL

NULL与索引

B-tree索引

-- 默认B-tree索引不包含NULL值的部分查询
CREATE INDEX idx_email ON users(email);

-- 查询NULL时可能不使用索引
SELECT * FROM users WHERE email IS NULL;

部分索引优化

-- 创建只包含非NULL值的索引
CREATE INDEX idx_email_not_null ON users(email)
WHERE email IS NOT NULL;

-- 创建只包含NULL值的索引
CREATE INDEX idx_email_null ON users((email IS NULL))
WHERE email IS NULL;

NULL与约束

NOT NULL约束

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,  -- 不允许NULL
    email VARCHAR(100)              -- 允许NULL
);

UNIQUE约束与NULL

-- UNIQUE允许多个NULL值
CREATE TABLE test (
    id SERIAL PRIMARY KEY,
    code VARCHAR(10) UNIQUE
);

INSERT INTO test (code) VALUES (NULL);
INSERT INTO test (code) VALUES (NULL);  -- 成功！允许多个NULL

CHECK约束与NULL

-- CHECK约束中NULL被视为通过
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    price DECIMAL CHECK (price > 0)
);

INSERT INTO products (price) VALUES (NULL);  -- 成功！

实际应用示例

处理可选字段

SELECT
    first_name,
    last_name,
    COALESCE(middle_name, '') as middle_name,
    COALESCE(phone, 'N/A') as phone
FROM customers;

条件替换

SELECT
    product_name,
    CASE
        WHEN stock IS NULL THEN 'Unknown'
        WHEN stock = 0 THEN 'Out of Stock'
        ELSE 'In Stock'
    END as availability
FROM products;

安全的数值计算

SELECT
    order_id,
    quantity * COALESCE(unit_price, 0) as line_total
FROM order_items;

最佳实践

1. 使用IS NULL：永远不要用= NULL比较
2. 善用COALESCE：提供默认值避免NULL传播
3. 注意聚合：了解聚合函数如何处理NULL
4. 文档化：记录哪些列允许NULL及其业务含义
5. 默认值：考虑使用DEFAULT而不是允许NULL