PostgreSQL - NULL 值

PostgreSQL - 处理 NULL 值

在 SQL 中，NULL 代表缺失、未知或不适用的值。它是一种状态，而不是一个具体的值。表字段中的 NULL 值显示为空白，表示没有输入任何数据。

理解 NULL 值与零（0）或包含空字符串（”）的字段不同至关重要。使用标准比较运算符（如 =、< 或 >）将 NULL 值与任何其他值（包括另一个 NULL）进行比较，结果都是“未知”（unknown），而不是真（true）或假（false）。这是 SQL 中三值逻辑（3VL）的核心概念。

定义允许 NULL 值的列

创建表时，列默认允许 NULL 值。您必须明确使用 NOT NULL 约束来防止 NULL 值。在下面的示例中，address 和 salary 列可以为 NULL。

CREATE TABLE employees (
    id INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    full_name VARCHAR(100) NOT NULL,
    age INT NOT NULL CHECK (age > 18),
    address VARCHAR(255),
    salary DECIMAL(10, 2)
);

• NOT NULL：此约束确保列始终必须具有一个值。
• 默认行为：没有 NOT NULL 约束的列可以包含 NULL 值。
• GENERATED ... AS IDENTITY：一种现代的、符合 SQL 标准的创建自增主键的方法（PostgreSQL 10 及更高版本可用）。

查询 NULL 值

因为 column = NULL 不会按预期工作，SQL 提供了 IS NULL 和 IS NOT NULL 运算符来专门测试 NULL 值的存在或缺失。我们使用一个 employees 表的示例：

id	full_name	age	address	salary
1	Alice Smith	32	123 Maple St	75000.00
2	Bob Johnson	25	456 Oak Ave	60000.00
3	Charlie Brown	29	789 Pine Ln	null
4	Diana Prince	35	null	120000.00

要查找所有薪水未记录的员工，我们使用 IS NULL：

SELECT id, full_name, age
FROM employees
WHERE salary IS NULL;

此查询将返回查理·布朗的记录：

id	full_name	age
3	Charlie Brown	29

要查找所有地址已知的员工，我们使用 IS NOT NULL：

SELECT id, full_name, address
FROM employees
WHERE address IS NOT NULL;

此查询将返回爱丽丝、鲍勃和查理的记录。

处理 NULL 值的函数

PostgreSQL 提供了有用的函数来在查询中处理 NULL 值，允许您用其他值替换它们。

COALESCE 函数

COALESCE 函数返回其参数中第一个非 NULL 的值。它非常适合为可能为 NULL 的列提供默认值。

-- 对于薪水为 NULL 的员工，显示“未提供薪水”。
SELECT full_name, COALESCE(salary::text, 'Salary Not Provided') AS salary_status
FROM employees;

结果：

full_name	salary_status
Alice Smith	75000.00
Bob Johnson	60000.00
Charlie Brown	Salary Not Provided
Diana Prince	120000.00

NULLIF 函数

NULLIF(value1, value2) 函数在 value1 等于 value2 时返回 NULL；否则，返回 value1。这对于数据清洗很有用，例如将空字符串转换为 NULL。

-- 假设 'department' 列可能包含空字符串 '' 而不是正确的 NULL 值。
-- 我们可以在 SELECT 查询中将其转换为 NULL。
SELECT full_name, NULLIF(department, '') AS cleaned_department
FROM employees;

常见错误和最佳实践

• 切勿使用 column = NULL 或 column != NULL。这些表达式总是评估为“未知”（在 WHERE 子句中其行为类似于 false）。始终使用 IS NULL 和 IS NOT NULL。
• 注意聚合函数。像 COUNT(column)、SUM(column) 和 AVG(column) 这样的函数在计算时会忽略 NULL 值。COUNT(*) 则会计算所有行，无论是否包含 NULL 值。
• 在适用情况下使用 NOT NULL 约束。如果某条信息对于记录有效至关重要（例如用户的电子邮件），则应在数据库层面通过 NOT NULL 约束强制执行。