PostgreSQL 教程: nullif 函数

八月 29, 2023

摘要：本教程向您展示如何使用 PostgreSQL 的 NULLIF 函数来处理空值。我们将向您展示一些使用 NULLIF 函数的示例。

PostgreSQL NULLIF 函数语法

NULLIF函数是 PostgreSQL 提供的最常见的条件表达式之一。下面说明了NULLIF函数的语法：

NULLIF(argument_1,argument_2);

如果argument_1等于argument_2则NULLIF函数返回空值，否则返回argument_1。

请查阅以下示例：

SELECT
	NULLIF (1, 1); -- return NULL

SELECT
	NULLIF (1, 0); -- return 1

SELECT
	NULLIF ('A', 'B'); -- return A

PostgreSQL NULLIF 函数示例

让我们看一下使用NULLIF函数的示例。

首先，我们创建一个表，名为posts，如下：

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

其次，我们插入一些示例数据到 posts 表中。

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 个字符。我们可以简单地使用以下查询来获取posts表中的所有行。

SELECT
	ID,
	title,
	excerpt
FROM
	posts;

PosgreSQL NULLIF - Posts table

我们在摘录列中看到空值。为了替换这个空值，我们可以使用 COALESCE 函数，如下所示：

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

PosgreSQL NULLIF - COALESCE

不幸的是，摘录列中存在空值和''（空字符串）的混合。这就是我们需要使用NULLIF函数的原因：

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

让我们更详细地检查该表达式：

首先，如果摘录为空，则NULLIF函数返回空值，否则返回摘录。NULLIF函数的结果由COALESCE函数使用。
其次，COALESCE函数检查由NULLIF函数提供的第一个参数是否为 null，如果为 null，则返回正文的前 40 个字符；否则，如果摘录不为空，则返回摘录。

使用 NULLIF 防止除零错误

使用NULLIF函数的另一个很好的例子是防止除零错误。让我们看一下下面的例子。

首先，我们创建一个新表，名为 members：

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;

为了计算男性成员的总数，我们使用 SUM 函数和 CASE 表达式。如果性别为 1，则CASE表达式返回 1，否则返回 0；该SUM函数用于计算男性成员的总数。同样的逻辑也适用于女性成员总数的计算。

然后将男性成员总数除以女性成员总数以返回比率。在本例中，它返回 200%，这是正确的。

PosgreSQL NULLIF - division by zero

第四步，我们删除女性成员：

DELETE
FROM
	members
WHERE
	gender = 2;

再次执行查询计算男女比例，我们得到以下错误信息：

ERROR:  division by zero

原因是女性人数为零。为了防止出现除零错误，我们使用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;