PostgreSQL 教程: coalesce 函数

八月 29, 2023

摘要:在本教程中,您将了解返回第一个非空参数的 PostgreSQL COALESCE 函数。您将学习如何在 SELECT 语句中应用此函数来有效处理空值。

目录

PostgreSQL COALESCE 函数语法

COALESCE函数的语法如下:

COALESCE (argument_1, argument_2, );

COALESCE函数接受无限数量的参数。它返回第一个不为空的参数。如果所有参数都为 null,则COALESCE函数将返回 null。

COALESCE函数从左到右计算参数,直到找到第一个非空参数。不会计算第一个非空参数以后的所有剩余参数。

COALESCE函数提供与 SQL 标准提供的NVLIFNULL函数相同的功能。MySQL 有IFNULL函数,而 Oracle 提供NVL函数。

请查阅以下示例:

SELECT
	COALESCE (1, 2);

postgresql COALESCE example 1

SELECT
	COALESCE (NULL, 2 , 1);

postgresql COALESCE example 2

当我们查询数据时,我们经常使用COLAESCE函数来替换空值的默认值。例如,我们要显示博客文章的摘录,如果未提供摘录,我们可以使用帖子内容的前 150 个字符。为了实现这一点,我们可以使用COALESCE函数,如下:

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

PostgreSQL COALESCE 示例

我们来看一个使用COALESCE函数的例子。首先,我们使用 CREATE TABLE 语句创建一个名为 items 的表,如下所示:

CREATE TABLE items (
	ID serial PRIMARY KEY,
	product VARCHAR (100) NOT NULL,
	price NUMERIC NOT NULL,
	discount NUMERIC
);

items表中有四个字段:

  • ID:标识 items 表中的项目的主键。
  • 产品:产品名称。
  • 价格:产品的价格。
  • 折扣:产品的折扣。

其次,我们使用 INSERT 语句将一些记录插入到 items 表中,如下所示:

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;

postgresql COALESCE example 3

如果您查看第四行,您会发现产品D的净价为空,这似乎不正确。问题是产品D的折扣为空,因此当我们采用空值计算净价时,PostgreSQL 返回空。

为了获得合适的价格,我们需要假设如果折扣为空,则折扣为零。然后我们可以使用COALESCE函数,如下:

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

postgresql COALESCE substitute null values

现在产品D的净价是500,因为我们在计算净价时使用的折扣为零而不是空值。

除了使用COALESCE函数之外,您还可以使用 CASE 表达式来处理这种情况下的空值。请参阅以下查询,该查询使用CASE表达式来实现与上面相同的结果。

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

在上面的查询中,如果折扣为空,则使用零 (0) 折扣值,否则在计算净价的表达式中直接使用折扣值。

从性能上来说,COALESCE函数和CASE表达式是一样的。我们更喜欢COALESCE函数而不是CASE表达式,因为COALESCE函数使查询更简短且更易于阅读。

在本教程中,您学习了如何使用COALESCE函数替换查询中的空值。