PostgreSQL 条件表达式详解

概述

PostgreSQL 中的条件表达式是 SQL 标准的重要组成部分，它们提供了在查询中进行逻辑判断和值选择的强大功能。本章将详细介绍四种主要的条件表达式：CASE、COALESCE、NULLIF、GREATEST 和 LEAST。

TIP

使用建议如果您的需求超出了这些条件表达式的功能范围，您可能需要考虑使用更具表达力的编程语言编写服务器端函数。

WARNING

重要提醒尽管 COALESCE、GREATEST 和 LEAST 在语法上与函数类似，但它们不是普通函数，因此不能与显式的 VARIADIC 数组参数一起使用。

1. CASE 表达式

1.1 基本概念

CASE 表达式是 SQL 中最灵活的条件表达式，类似于其他编程语言中的 if/else 语句。它提供了两种语法形式：通用形式和简单形式。

1.2 通用 CASE 语法

sql

CASE WHEN condition THEN result
     [WHEN condition THEN result ...]
     [ELSE result]
END

语法结构说明

实际应用示例

创建测试表薪资等级分类

sql

-- 创建员工薪资表
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    salary INTEGER,
    department VARCHAR(30)
);

-- 插入测试数据
INSERT INTO employees (name, salary, department) VALUES
('张三', 15000, '技术部'),
('李四', 8000, '销售部'),
('王五', 25000, '管理层'),
('赵六', 6000, '客服部');

sql

-- 根据薪资划分员工等级
SELECT
    name,
    salary,
    CASE
        WHEN salary >= 20000 THEN '高级'
        WHEN salary >= 10000 THEN '中级'
        WHEN salary >= 5000 THEN '初级'
        ELSE '实习生'
    END as salary_level
FROM employees;

查询结果：

name	salary	salary_level
张三	15000	中级
李四	8000	初级
王五	25000	高级
赵六	6000	初级

1.3 简单 CASE 语法

sql

CASE expression
    WHEN value1 THEN result1
    [WHEN value2 THEN result2 ...]
    [ELSE result]
END

实际应用示例

sql

-- 部门名称本地化
SELECT
    name,
    department,
    CASE department
        WHEN '技术部' THEN 'Technology'
        WHEN '销售部' THEN 'Sales'
        WHEN '管理层' THEN 'Management'
        WHEN '客服部' THEN 'Customer Service'
        ELSE 'Unknown'
    END as department_en
FROM employees;

查询结果：

name	department	department_en
张三	技术部	Technology
李四	销售部	Sales
王五	管理层	Management
赵六	客服部	Customer Service

1.4 CASE 表达式的高级应用

聚合函数中的条件统计

sql

-- 统计不同薪资等级的员工数量
SELECT
    COUNT(*) as total_employees,
    COUNT(CASE WHEN salary >= 20000 THEN 1 END) as high_level,
    COUNT(CASE WHEN salary >= 10000 AND salary < 20000 THEN 1 END) as mid_level,
    COUNT(CASE WHEN salary < 10000 THEN 1 END) as low_level
FROM employees;

避免除零错误

sql

-- 安全的除法运算
SELECT
    name,
    salary,
    CASE
        WHEN salary > 0 THEN (salary * 12) / salary  -- 年薪计算示例
        ELSE 0
    END as annual_multiplier
FROM employees;

WARNING

计算顺序注意事项如第 4.2.14 节中所述，在各种情况下，表达式的子表达式会在不同的时间进行计算，因此"CASE 仅计算必要的子表达式"的原则并非铁律。例如，即使常量 1/0 子表达式在运行时永远不会进入的 CASE 分支中，通常也会在计划时导致除零错误。

2. COALESCE 函数

2.1 基本概念

COALESCE 函数返回其参数中第一个非 NULL 的值，这在处理空值和提供默认值时非常有用。

2.2 语法结构

sql

COALESCE(value1, value2, value3, ...)

工作流程图

2.3 实际应用示例

用户信息显示

创建用户表智能显示名称

sql

-- 创建用户信息表
CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    full_name VARCHAR(100),
    nickname VARCHAR(50),
    display_name VARCHAR(100)
);

-- 插入测试数据（包含各种 NULL 情况）
INSERT INTO user_profiles (username, full_name, nickname, display_name) VALUES
('john_doe', '约翰·多伊', NULL, NULL),
('jane_smith', NULL, '小简', NULL),
('bob_wilson', NULL, NULL, '鲍勃'),
('alice_brown', '爱丽丝·布朗', '小爱', '爱丽丝'),
('unknown_user', NULL, NULL, NULL);

sql

-- 智能选择显示名称：优先级 display_name > nickname > full_name > username
SELECT
    username,
    COALESCE(display_name, nickname, full_name, username, '匿名用户') as display_name
FROM user_profiles;

查询结果：

username	display_name
john_doe	约翰·多伊
jane_smith	小简
bob_wilson	鲍勃
alice_brown	爱丽丝
unknown_user	unknown_user

配置参数默认值

sql

-- 系统配置表
CREATE TABLE system_config (
    config_key VARCHAR(50),
    user_value TEXT,
    system_default TEXT
);

INSERT INTO system_config VALUES
('theme', NULL, 'light'),
('language', 'zh-CN', 'en-US'),
('timeout', NULL, '300');

-- 获取有效配置值
SELECT
    config_key,
    COALESCE(user_value, system_default, 'undefined') as effective_value
FROM system_config;

2.4 COALESCE 与其他数据库的兼容性

INFO

数据库兼容性此 SQL 标准函数提供的功能类似于某些其他数据库系统中使用的 NVL（Oracle）和 IFNULL（MySQL）。

数据库系统	等价函数	示例
PostgreSQL	COALESCE	`COALESCE(a, b, c)`
Oracle	NVL / NVL2	`NVL(a, b)`
MySQL	IFNULL / COALESCE	`IFNULL(a, b)`
SQL Server	ISNULL / COALESCE	`ISNULL(a, b)`

3. NULLIF 函数

3.1 基本概念

NULLIF 函数在两个值相等时返回 NULL，否则返回第一个值。它常用于将特定值转换为 NULL，是 COALESCE 的逆操作。

3.2 语法结构

sql

NULLIF(value1, value2)

逻辑流程图

3.3 实际应用示例

数据清洗场景

创建产品表数据清洗查询

sql

-- 创建产品信息表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    description TEXT,
    category VARCHAR(50),
    status VARCHAR(20)
);

-- 插入测试数据（包含需要清洗的数据）
INSERT INTO products (name, description, category, status) VALUES
('笔记本电脑', '高性能办公笔记本', '电子产品', 'active'),
('无线鼠标', '(none)', '电子产品', 'active'),
('键盘', '', '电子产品', 'inactive'),
('显示器', 'N/A', '电子产品', 'active'),
('音响', '高品质音响设备', '电子产品', '(empty)');

sql

-- 将特殊标记值转换为 NULL
SELECT
    name,
    NULLIF(NULLIF(NULLIF(description, '(none)'), ''), 'N/A') as clean_description,
    NULLIF(status, '(empty)') as clean_status
FROM products;

查询结果：

name	clean_description	clean_status
笔记本电脑	高性能办公笔记本	active
无线鼠标	NULL	active
键盘	NULL	inactive
显示器	NULL	active
音响	高品质音响设备	NULL

统计分析中的应用

sql

-- 计算非零销售额的平均值
CREATE TABLE sales_data (
    product_id INTEGER,
    sales_amount DECIMAL(10,2)
);

INSERT INTO sales_data VALUES
(1, 1000.00),
(2, 0.00),      -- 零销售额
(3, 1500.50),
(4, 0.00),      -- 零销售额
(5, 2000.00);

-- 计算平均值时排除零值
SELECT
    AVG(NULLIF(sales_amount, 0)) as avg_non_zero_sales,
    AVG(sales_amount) as avg_all_sales
FROM sales_data;

3.4 NULLIF 的数据类型处理

Details

类型转换细节结果的类型与第一个参数相同——但存在一个微妙之处。实际返回的是隐含的 = 运算符的第一个参数，在某些情况下，该参数将被提升以匹配第二个参数的类型。

例如：NULLIF(1, 2.2) 产生 numeric 类型，因为没有 integer = numeric 运算符，只有 numeric = numeric。

sql

-- 演示类型转换
SELECT
    NULLIF(1, 2.2) as result,           -- 返回 numeric 类型的 1
    pg_typeof(NULLIF(1, 2.2)) as type_name;

4. GREATEST 和 LEAST 函数

4.1 基本概念

GREATEST 和 LEAST 函数分别用于从多个值中选择最大值和最小值。它们在数据比较和范围限制方面非常有用。

4.2 语法结构

sql

GREATEST(value1, value2, value3, ...)
LEAST(value1, value2, value3, ...)

工作原理图

4.3 实际应用示例

学生成绩分析

创建成绩表成绩统计分析

sql

-- 创建学生成绩表
CREATE TABLE student_scores (
    student_id INTEGER,
    student_name VARCHAR(50),
    math_score INTEGER,
    english_score INTEGER,
    science_score INTEGER,
    history_score INTEGER
);

-- 插入测试数据
INSERT INTO student_scores VALUES
(1, '张三', 85, 78, 92, 88),
(2, '李四', 76, 85, NULL, 82),  -- 科学成绩缺失
(3, '王五', 92, 88, 95, 90),
(4, '赵六', NULL, NULL, NULL, NULL);  -- 所有成绩缺失

sql

-- 计算每个学生的最高分和最低分
SELECT
    student_name,
    GREATEST(math_score, english_score, science_score, history_score) as highest_score,
    LEAST(math_score, english_score, science_score, history_score) as lowest_score,
    -- 计算成绩范围
    GREATEST(math_score, english_score, science_score, history_score) -
    LEAST(math_score, english_score, science_score, history_score) as score_range
FROM student_scores;

查询结果：

student_name	highest_score	lowest_score	score_range
张三	92	78	14
李四	85	76	9
王五	95	88	7
赵六	NULL	NULL	NULL

价格比较和优惠计算

sql

-- 创建产品价格表
CREATE TABLE product_prices (
    product_name VARCHAR(100),
    store_a_price DECIMAL(8,2),
    store_b_price DECIMAL(8,2),
    store_c_price DECIMAL(8,2),
    online_price DECIMAL(8,2)
);

INSERT INTO product_prices VALUES
('笔记本电脑', 5999.00, 6299.00, 5899.00, 5799.00),
('无线鼠标', 199.00, NULL, 189.00, 179.00),
('机械键盘', 899.00, 999.00, 849.00, 829.00);

-- 找出最优价格和最高价格
SELECT
    product_name,
    LEAST(store_a_price, store_b_price, store_c_price, online_price) as best_price,
    GREATEST(store_a_price, store_b_price, store_c_price, online_price) as highest_price,
    -- 计算最大优惠金额
    GREATEST(store_a_price, store_b_price, store_c_price, online_price) -
    LEAST(store_a_price, store_b_price, store_c_price, online_price) as max_savings
FROM product_prices;

4.4 与 SQL 标准的差异

WARNING

SQL 标准差异将忽略参数列表中的 NULL 值。仅当所有表达式都计算为 NULL 时，结果才为 NULL。

这是与 SQL 标准的偏差。根据标准，如果任何参数为 NULL，则返回值也为 NULL。某些其他数据库的行为方式就是这样。

不同数据库的 NULL 处理比较

数据库系统	NULL 处理方式	示例结果
PostgreSQL	忽略 NULL 值	`GREATEST(1, NULL, 3)` → `3`
MySQL	忽略 NULL 值	`GREATEST(1, NULL, 3)` → `3`
SQL Server	任何 NULL 返回 NULL	`GREATEST(1, NULL, 3)` → `NULL`
Oracle	任何 NULL 返回 NULL	`GREATEST(1, NULL, 3)` → `NULL`

5. 性能优化和最佳实践

5.1 性能考量

索引利用

sql

-- 创建带索引的表
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status VARCHAR(20),
    priority INTEGER,
    created_at TIMESTAMP
);

CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_priority ON orders(priority);

-- 优化的 CASE 查询（可以利用索引）
SELECT * FROM orders
WHERE status = CASE
    WHEN priority >= 5 THEN 'urgent'
    ELSE 'normal'
END;

-- 更好的方式：分别查询
SELECT * FROM orders WHERE priority >= 5 AND status = 'urgent'
UNION ALL
SELECT * FROM orders WHERE priority < 5 AND status = 'normal';

避免重复计算

sql

-- 低效的写法：重复计算
SELECT
    product_name,
    CASE
        WHEN (price * quantity * tax_rate) > 1000 THEN 'expensive'
        WHEN (price * quantity * tax_rate) > 500 THEN 'moderate'
        ELSE 'cheap'
    END as price_category,
    (price * quantity * tax_rate) as total_price
FROM order_items;

-- 高效的写法：使用子查询避免重复计算
SELECT
    product_name,
    CASE
        WHEN total_price > 1000 THEN 'expensive'
        WHEN total_price > 500 THEN 'moderate'
        ELSE 'cheap'
    END as price_category,
    total_price
FROM (
    SELECT
        product_name,
        (price * quantity * tax_rate) as total_price
    FROM order_items
) t;

5.2 数据类型一致性

TIP

最佳实践所有 result 表达式的数据类型必须可转换为单一输出类型。确保类型兼容性可以避免意外的类型转换和性能问题。

sql

-- 正确的类型使用
SELECT
    CASE
        WHEN score >= 90 THEN '优秀'::TEXT
        WHEN score >= 80 THEN '良好'::TEXT
        WHEN score >= 60 THEN '及格'::TEXT
        ELSE '不及格'::TEXT
    END as grade
FROM student_scores;

-- 避免混合类型
-- 错误示例：混合了 TEXT 和 INTEGER
SELECT
    CASE
        WHEN score >= 90 THEN '优秀'
        WHEN score >= 80 THEN 80    -- 这会导致类型问题
        ELSE '不及格'
    END as grade
FROM student_scores;

6. 综合应用案例

6.1 电商订单状态管理

sql

-- 创建订单表
CREATE TABLE ecommerce_orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    order_status VARCHAR(20),
    payment_status VARCHAR(20),
    ship_status VARCHAR(20),
    order_date TIMESTAMP,
    payment_date TIMESTAMP,
    ship_date TIMESTAMP,
    total_amount DECIMAL(10,2)
);

-- 插入测试数据
INSERT INTO ecommerce_orders VALUES
(1, 101, 'completed', 'paid', 'delivered', '2024-01-15', '2024-01-15', '2024-01-18', 299.99),
(2, 102, 'processing', 'paid', 'pending', '2024-01-16', '2024-01-16', NULL, 199.50),
(3, 103, 'cancelled', 'refunded', NULL, '2024-01-17', '2024-01-17', NULL, 399.00),
(4, 104, 'pending', 'pending', NULL, '2024-01-18', NULL, NULL, 159.99);

-- 综合状态分析查询
SELECT
    order_id,
    customer_id,

    -- 使用 CASE 确定订单优先级
    CASE
        WHEN total_amount >= 300 THEN '高优先级'
        WHEN total_amount >= 200 THEN '中优先级'
        ELSE '普通优先级'
    END as priority,

    -- 使用 COALESCE 处理空值显示
    COALESCE(
        CASE
            WHEN order_status = 'completed' THEN '订单完成'
            WHEN order_status = 'processing' THEN '处理中'
            WHEN order_status = 'cancelled' THEN '已取消'
            ELSE '待处理'
        END,
        '状态未知'
    ) as status_display,

    -- 使用 NULLIF 和 COALESCE 组合处理日期
    COALESCE(
        ship_date::TEXT,
        NULLIF(payment_date::TEXT, ''),
        '未处理'
    ) as last_action_date,

    -- 使用 GREATEST 计算处理时长
    GREATEST(
        EXTRACT(EPOCH FROM (COALESCE(ship_date, payment_date, order_date) - order_date))/86400,
        0
    ) as processing_days,

    total_amount

FROM ecommerce_orders
ORDER BY order_id;

6.2 数据质量检查和修复

sql

-- 创建用户数据表（包含数据质量问题）
CREATE TABLE user_data_raw (
    user_id SERIAL PRIMARY KEY,
    email VARCHAR(100),
    phone VARCHAR(20),
    age INTEGER,
    city VARCHAR(50),
    registration_source VARCHAR(30)
);

-- 插入包含问题的数据
INSERT INTO user_data_raw (email, phone, age, city, registration_source) VALUES
('user1@example.com', '13800138000', 25, '北京', 'website'),
('', '13900139000', 30, 'unknown', 'mobile'),
('user3@example.com', 'N/A', -5, '上海', ''),
('invalid-email', '13700137000', 150, '', 'social'),
(NULL, NULL, NULL, NULL, NULL);

-- 数据清洗和标准化
SELECT
    user_id,

    -- 邮箱清洗：移除无效值
    NULLIF(
        CASE
            WHEN email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' THEN email
            ELSE NULL
        END,
        ''
    ) as clean_email,

    -- 电话清洗：标准化格式
    CASE
        WHEN NULLIF(phone, '') ~ '^\d{11}$' THEN phone
        WHEN NULLIF(phone, 'N/A') IS NULL THEN NULL
        ELSE NULL
    END as clean_phone,

    -- 年龄验证：合理范围
    CASE
        WHEN age BETWEEN 0 AND 120 THEN age
        ELSE NULL
    END as clean_age,

    -- 城市标准化
    COALESCE(
        NULLIF(NULLIF(city, ''), 'unknown'),
        '未知城市'
    ) as clean_city,

    -- 注册来源标准化
    COALESCE(
        NULLIF(registration_source, ''),
        'unknown'
    ) as clean_source,

    -- 数据完整性评分
    CASE
        WHEN (email IS NOT NULL AND email != '')
             AND phone ~ '^\d{11}$'
             AND age BETWEEN 0 AND 120
             AND (city IS NOT NULL AND city != '' AND city != 'unknown')
        THEN '完整'
        WHEN (email IS NOT NULL AND email != '') OR phone ~ '^\d{11}$'
        THEN '部分完整'
        ELSE '不完整'
    END as data_quality_score

FROM user_data_raw;

7. 总结

PostgreSQL 的条件表达式提供了强大而灵活的数据处理能力：

主要特点对比

函数	主要用途	特点	应用场景
`CASE`	多条件判断	最灵活，支持复杂逻辑	数据分类、状态转换、复杂计算
`COALESCE`	空值处理	返回第一个非空值	默认值设置、数据合并
`NULLIF`	值转换	相等时返回 NULL	数据清洗、特殊值处理
`GREATEST/LEAST`	值比较	选择最值，忽略 NULL	范围限制、优惠计算、统计分析

最佳实践要点

性能优化：避免在大型表的 WHERE 子句中使用复杂的 CASE 表达式
类型一致性：确保所有分支返回兼容的数据类型
NULL 处理：充分利用各函数的 NULL 处理特性
可读性：对于复杂逻辑，考虑使用 CTE 或子查询提高可读性
测试验证：在生产环境使用前，充分测试边界情况和 NULL 值处理

通过熟练掌握这些条件表达式，可以编写出更加高效、健壮和易维护的 SQL 查询语句。

PostgreSQL 条件表达式详解 ​

概述 ​

1. CASE 表达式 ​

1.1 基本概念 ​

1.2 通用 CASE 语法 ​

语法结构说明 ​

实际应用示例 ​

1.3 简单 CASE 语法 ​

实际应用示例 ​

1.4 CASE 表达式的高级应用 ​

聚合函数中的条件统计 ​

避免除零错误 ​

2. COALESCE 函数 ​

2.1 基本概念 ​

2.2 语法结构 ​

工作流程图 ​

2.3 实际应用示例 ​

用户信息显示 ​

配置参数默认值 ​

2.4 COALESCE 与其他数据库的兼容性 ​

3. NULLIF 函数 ​

3.1 基本概念 ​

3.2 语法结构 ​

逻辑流程图 ​

3.3 实际应用示例 ​

数据清洗场景 ​

统计分析中的应用 ​

3.4 NULLIF 的数据类型处理 ​

4. GREATEST 和 LEAST 函数 ​

4.1 基本概念 ​

4.2 语法结构 ​

工作原理图 ​

4.3 实际应用示例 ​

学生成绩分析 ​

价格比较和优惠计算 ​

4.4 与 SQL 标准的差异 ​

不同数据库的 NULL 处理比较 ​

5. 性能优化和最佳实践 ​

5.1 性能考量 ​

索引利用 ​

避免重复计算 ​

5.2 数据类型一致性 ​

6. 综合应用案例 ​

6.1 电商订单状态管理 ​

6.2 数据质量检查和修复 ​

7. 总结 ​

主要特点对比 ​

最佳实践要点 ​

PostgreSQL 条件表达式详解

概述

1. CASE 表达式

1.1 基本概念

1.2 通用 CASE 语法

语法结构说明

实际应用示例

1.3 简单 CASE 语法

实际应用示例

1.4 CASE 表达式的高级应用

聚合函数中的条件统计

避免除零错误

2. COALESCE 函数

2.1 基本概念

2.2 语法结构

工作流程图

2.3 实际应用示例

用户信息显示

配置参数默认值

2.4 COALESCE 与其他数据库的兼容性

3. NULLIF 函数

3.1 基本概念

3.2 语法结构

逻辑流程图

3.3 实际应用示例

数据清洗场景

统计分析中的应用

3.4 NULLIF 的数据类型处理

4. GREATEST 和 LEAST 函数

4.1 基本概念

4.2 语法结构

工作原理图

4.3 实际应用示例

学生成绩分析

价格比较和优惠计算

4.4 与 SQL 标准的差异

不同数据库的 NULL 处理比较

5. 性能优化和最佳实践

5.1 性能考量

索引利用

避免重复计算

5.2 数据类型一致性

6. 综合应用案例

6.1 电商订单状态管理

6.2 数据质量检查和修复

7. 总结

主要特点对比

最佳实践要点