本章将讨论 PostgreSQL 条件表达式和运算符，包括：使用 CASE 构造条件查询、使用 COALESCE 过滤非空参数、使用 NULLIF 处理 NULL 值以及使用 CAST 进行数据类型转换。
CASE
CASE和其它编程语言中的 IF/ELSE 相当：用于构造条件查询。它是一个表达式，因此可以在任何使用表达式的地方使用它，比如：SELECT、WHERE、GROUP BY 和 HAVING。
它有两种形式：通用形式和简单形式。
通用形式
CASE 的通用形式的语法如下 ：
- 每个条件（
条件1、条件2……）都是返回TRUE或FALSE的布尔表达式 - 当某个条件的结果是
TRUE时，整个CASE的返回值就是对应的结果。比如，当条件1为TRUE时，整个CASE的返回值就是结果1 - 当某个条件的结果是
FALSE时，CASE从上到下计算一下个条件，直到结果为TRUE - 如果所有条件都是
FALSE，则返回ELSE后面的其它结果- 如果没有提供
ELSE，则返回NULL
- 如果没有提供
示例：根据时长返回不同的时长说明
SELECT title,
length,
CASE
WHEN length> 0
AND length <= 50 THEN '短视频'
WHEN length > 50
AND length <= 120 THEN '中等长度'
WHEN length> 120 THEN '长视频'
END AS duration
FROM film
ORDER BY title;
SELECT
SUM (CASE
WHEN rental_rate = 0.99 THEN 1
ELSE 0
END
) AS "经济型",
SUM (
CASE
WHEN rental_rate = 2.99 THEN 1
ELSE 0
END
) AS "大众型",
SUM (
CASE
WHEN rental_rate = 4.99 THEN 1
ELSE 0
END
) AS "精英型"
FROM
film;
简单形式
PostgreSQL 提供了另一种称为”简单形式“的 CASE：
CASE 表达式
WHEN 值1 THEN 结果1
WHEN 值2 THEN 结果2
[WHEN ...]
ELSE
其它结果
END
- 首先计算
表达式的值 - 然后将这个值分别与每个
WHEN进行对比，如果等于某个WHEN值，就返回对应的结果。比如，如果等于值1就返回结果1 - 如果所有的
WHEN都不满足表达式的值，则返回ELSE后面的其它结果。如果没有提供ELSE，则返回NULL
示例：返回评级的说明
SELECT title,
rating,
CASE rating
WHEN 'G' THEN '一般观众'
WHEN 'PG' THEN '建议家长指导'
WHEN 'PG-13' THEN '家长强烈警告'
WHEN 'R' THEN '受限制的'
WHEN 'NC-17' THEN '仅限成年人'
END rating_description
FROM film
ORDER BY title;
示例二：结合聚合函数返回评级说明：
SELECT
SUM(CASE rating
WHEN 'G' THEN 1
ELSE 0
END) "一般观众",
SUM(CASE rating
WHEN 'PG' THEN 1
ELSE 0
END) "建议家长指导",
SUM(CASE rating
WHEN 'PG-13' THEN 1
ELSE 0
END) "家长强烈警告",
SUM(CASE rating
WHEN 'R' THEN 1
ELSE 0
END) "受限制的",
SUM(CASE rating
WHEN 'NC-17' THEN 1
ELSE 0
END) "仅限成年人"
FROM film;
COALESCE
该函数接收不限个数的参数，返回第一个不为空（NULL）的参数的值。如果所有参数均为 NULL，则函数也返回 NULL。一旦发现非空参数，直接返回该参数的值，而不再处理后面的参数。
它和 MySQL 的 IFNULL 函数的功能相同。
示例数据：
CREATE TABLE items (
ID serial PRIMARY KEY,
product VARCHAR (100) NOT NULL,
price NUMERIC NOT NULL,
discount NUMERIC
);
INSERT INTO items (product, price, discount)
VALUES
('A', 1000 ,10),
('B', 1500 ,20),
('C', 800 ,5),
('D', 500, NULL);
查询产品净价：
SELECT
product,
(price - discount) AS net_price
FROM
items;
结果并不正确，因为产品D的折扣是 NULL，而【万恶】的 NULL 无论参与何种运算，结果都是 NULL。
我们进行改良，如果折扣为 NULL，那就设置为 0：
SELECT
product,
(price - COALESCE(discount,0)) AS net_price
FROM
items;
结果终于正确了。
我们可以使用 CASE 来模拟上面的 COALESCE：
SELECT
product,
(
price - CASE
WHEN discount IS NULL THEN
0
ELSE
discount
END
) AS net_price
FROM
items;
NULLIF
语法：
NULLIF(参数1,参数2);
如果两个参数的值相等，返回 NULL；否则返回 参数1
比如：
SELECT
NULLIF (1, 1); -- 返回 NULL
SELECT
NULLIF (1, 0); -- 返回 1
SELECT
NULLIF ('A', 'B'); -- 返回 A
示例数据：
CREATE TABLE posts (
id serial primary key,
title VARCHAR (255) NOT NULL,
excerpt VARCHAR (150),
body TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP
);
INSERT INTO posts (title, excerpt, body)
VALUES
('test post 1','test post excerpt 1','test post body 1'),
('test post 2','','test post body 2'),
('test post 3', null ,'test post body 3');
SELECT
id,
title,
COALESCE (
NULLIF (excerpt, ''),
LEFT (body, 40)
)
FROM
posts;
示例2：防止被零除
CREATE TABLE members (
ID serial PRIMARY KEY,
first_name VARCHAR (50) NOT NULL,
last_name VARCHAR (50) NOT NULL,
gender SMALLINT NOT NULL -- 1: 男, 2 女
);
INSERT INTO members (
first_name,
last_name,
gender
)
VALUES
('John', 'Doe', 1),
('David', 'Dave', 1),
('Bush', 'Lily', 2);
SELECT
(
SUM (
CASE
WHEN gender = 1 THEN
1
ELSE
0
END
) / NULLIF (
SUM (
CASE
WHEN gender = 2 THEN
1
ELSE
0
END
),
0
)
) * 100 AS "Male/Female ratio"
FROM
members;
CAST
CAST 用于将表达式的值转换为目标数据类型：
CAST(表达式 AS 目标数据类型);
PostgreSQL 提供了等价的 :: 运算符用于简化操作：
表达式::目标数据类型;
比如：
SELECT '100'::INTEGER, '2023-07-31'::DATE;
示例：
-- 将字符串转换成整数
SELECT '100'::INTEGER;
-- 将字符串转换成日期
SELECT '2023-07-31'::DATE;
-- 将字符串转换成浮点
SELECT '10.2'::FLOAT;
-- 将字符串转换成布尔
SELECT 'true'::BOOLEAN, 'false'::BOOLEAN, 'T'::BOOLEAN, 'NO'::BOOLEAN;
-- 将字符串转换成时间戳
SELECT '2019-06-15 12:32:43'::TIMESTAMP;
-- 将字符串转换成时间间隔
SELECT '15 minute'::interval,
'2 hour'::interval,
'1 day'::interval,
'2 week'::interval,
'3 month'::interval;