PostgreSQL COALESCE 和 NULLIF 函数

admin管理员组

文章数量:1794759

PostgreSQL COALESCE 和 NULLIF 函数

本文学习PostgreSQL COALESCE 和 NULLIF函数。COALESCE它返回第一个非空参数，并通过示例让你了解如何在select语句中有效处理空值。NULLIF需要两个参数，参数相同返回null，否则返回第一个参数。通过同时学习两个函数，可以区分两者，同时可以组合使用，增强你SQL的健壮性。

PostgreSQL COALESCE 函数语法

​ COALESCE 函数语法:

COALESCE (argument_1, argument_2, …);

​ 参数可以有无限个，总是返回第一个非空参数，如果所有参数都是null，则返回null。

COALESCE 函数从左到右开始评估每个参数，直到发现第一个非空参数，所有剩余参数被忽略不会被评估。标准SQL中对应的函数为 NVL 和 IFNULL ，mysql 为 ifnull 函数， oracle 为nvl 函数。

请看示例：

SELECT COALESCE(1, 2); -- return 1 SELECT COALESCE(NULL, 2 , 1); -- return 2

通常在查询语句中使用 COALESCE函数使用缺省值代替null值。假设我们要展示blog的摘要信，如果没有摘要则去内容的前150字符作为摘要，使用COALESCE函数实现：

SELECT COALESCE (excerpt, LEFT(CONTENT, 150)) excerpt FROM posts; PostgreSQL COALESCE 示例

下面通过示例展示 COALESCE 函数的用法，首先创建表：

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);

现在需要查询产品的净价（实际价格），使用下面公示：

-- net_price = price - discount; SELECT product, (price - discount) AS net_price FROM items;

返回结果：

productnet_price

A	990
B	1480
C	795
D

我们注意到最后一行产品D的净价为0。问题是因为折扣字段值为null 造成的，因为PostgreSQL计算遇到null值会返回null。为了获得正确结果，我们需要假设折扣为空，即没有折扣或为0。我们使用coalesce 函数实现：

SELECT product, (price - COALESCE(discount,0)) AS net_price FROM items;

返回结果：

productnet_price

A	990
B	1480
C	795
D	500

现在D的净价为500，因为计算时使用0代替折扣null值。除了使用 coalesce 函数，我们也可以使用 case 表达式处理null值。请看示例：

SELECT product, ( price - CASE WHEN discount IS NULL THEN 0 ELSE discount END ) AS net_price FROM items;

返回结果一致。从性能上看两者相同。当推荐使用 COALESCE 函数，它比 CASE 表达式更简洁易读。

NULLIF 函数语法

NULLIF 函数是PostgreSQL提供的最常用的条件表达式之一，语法如下：

NULLIF(argument_1,argument_2);

如果两个参数相等返回null，否则返回第一个参数。请看示例：

SELECT NULLIF (1, 1); -- return NULL SELECT NULLIF (1, 0); -- return 1 SELECT NULLIF ('A', 'B'); -- return A NULLIF 函数示例

下面通过示例学习 nullif函数，首先创建表：

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');

我们的需求是显示博客列表页面，显示标题和摘要信。当记录没有摘要时，我们取内容的前40个字符作为摘要。下面简单使用语句实现：

SELECT ID, title, excerpt FROM posts;

返回结果：

idtitleexcerpt

1	test post 1	test post excerpt 1
2	test post 2
3	test post 3

excerpt 字段的值 ：id为2 是 空串，id为3是为 null 。因为有null值，我们使用上面提到的coalesce函数实现：

SELECT id, title, COALESCE (excerpt, LEFT(body, 40)) excerpt FROM posts;

返回结果：

idtitleexcerpt

1	test post 1	test post excerpt 1
2	test post 2
3	test post 3	test post body 3

因为同时有null值 和 空串，我们混合使用 nullif函数：

SELECT id, title, COALESCE ( NULLIF (excerpt, ''), LEFT (body, 40) ) FROM posts;

首先，如果excerpt是空串则nullif 函数返回null，否则它返回 excerpt 。nullif函数返回结果给coalesce使用。

其次，coalesce 函数检查第一个参数，它有nullif函数提供的，如果为null则返回博客内容前40字符，否则返回 excerpt本身（此时excerpt不为null）。

返回结果：

idtitlecoalesce

1	test post 1	test post excerpt 1
2	test post 2	test post body 2
3	test post 3	test post body 3

使用nullif函数防止除数为零

首先创建表：

CREATE TABLE members ( ID serial PRIMARY KEY, first_name VARCHAR (50) NOT NULL, last_name VARCHAR (50) NOT NULL, gender SMALLINT NOT NULL -- 1: male, 2 female );

插入示例数据：

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 ) / SUM ( CASE WHEN gender = 2 THEN 1 ELSE 0 END ) ) * 100 AS "Male/Female ratio" FROM members;

因为 gender = 1 有两条， 2 有1条，返回值 为 200% ,结果正确。现在删除female记录：

DELETE FROM members WHERE gender = 2;

再次执行上面查询返回错误，因为除数为零。让我们使用nullif函数进行修正：

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;

使用nullif函数检查sum函数值是否为0，如果为 0 则返回null ，从而整个结果为null ，成功避免了除数为 0 .

总结

本文介绍了COALESCE 和 NULLIF 函数，通过对比学习，可以在合适的场景中组合使用增强你的SQL能力。

本文标签： 函数postgresqlCOALESCEnullif