PostgreSQL COALESCE 和 NULLIF 函数_pgsql ifnull_梦想画家的博客

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
	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;
返回结果： 
product net_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;
返回结果： 
product net_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
        ) 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;
返回结果： 
id title excerpt
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;
返回结果： 
id title excerpt
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）。 
返回结果： 
id title coalesce
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 (
            WHEN gender = 1 THEN 1
            ELSE 0
	) / SUM (
            WHEN gender = 2 THEN 1
            ELSE 0
	) ) * 100 AS "Male/Female ratio"
	members;
因为 gender = 1 有两条， 2 有1条，返回值 为 200% ,结果正确。现在删除female记录： 
DELETE FROM members
WHERE gender = 2;
再次执行上面查询返回错误，因为除数为零。让我们使用nullif函数进行修正： 
SELECT
		SUM (
                WHEN gender = 1 THEN 1
                ELSE 0
		) / NULLIF (
			SUM (
                    WHEN gender = 2 THEN 1
                    ELSE 0
			), 0
	) * 100 AS "Male/Female ratio"
FROM members;
使用nullif函数检查sum函数值是否为0，如果为 0 则返回null ，从而整个结果为null ，成功避免了除数为 0 . 
本文介绍了COALESCE 和 NULLIF 函数，通过对比学习，可以在合适的场景中组合使用增强你的SQL能力。
                    本文学习PostgreSQL COALESCE 和 NULLIF函数。COALESCE它返回第一个非空参数，并通过示例让你了解如何在select语句中有效处理空值。NULLIF需要两个参数，参数相同返回null，否则返回第一个参数。通过同时学习两个函数，可以区分两者，同时可以组合使用，增强你SQL的健壮性。PostgreSQL COALESCE 函数语法	COALESCE 函数语法:COALESCE (argument_1, argument_2, …);	参数可以有无限个，总是返回第一个.
				一、postgresql中条件表达式
今天上班的时候，有一个哥们跑过来问我，pg有没有行转列的函数，如何实现行转列，他说他pg中没有找到，问我知道不，我想了一下，好像有,不过不像SQLserver 那么好用，但行转列很简单啊，使用多个union all 就简单搞定了啊，于是他给我看了他的问题
select id,a,b,c,d,e,f,g
from biztable;
想比较 a,b,c,d,e...
工作中迁移mysql至pg 9.6，遇到mysql中的ifnull函数在pg中没有，pg中函数coalesce与ifnull功能相同，但函数名不同，需要修改应用。ifnull也在SQL标准中，pg此处不符合sql标准规范。本人尝试修改pg源码添加了ifnull函数，在此做一分享，不当之处请各位批评指正。
ifnull语法规范
语法格式：
IFNULL(expr1 任意类型, expr2 任意...
postgresql中的coalesce函数用于返回参数列表中第一个非空值。如果所有参数都是空值，则返回null。
例如，coalesce（null，1，2）将返回1，因为1是第一个非空值。如果所有参数都是null，则coalesce（null，null，null）将返回null。   
### 回答2：
### 回答3：
id	title	excerpt
1	test post 1	test post excerpt 1
2	test post 2
3	test post 3	test post body 3