PostgreSQL 教程: coalesce 函数

八月 29, 2023

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

PostgreSQL COALESCE 函数语法

COALESCE函数的语法如下：

COALESCE (argument_1, argument_2, …);

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

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

COALESCE函数提供与 SQL 标准提供的NVL或IFNULL函数相同的功能。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函数替换查询中的空值。

搜索

分类

标签