PG在转换null值时，需要注意 CASE WHEN与 COALESCE 的区别

目录

• 适用范围
• 方案概述
• 实施步骤
  • 在ORACLE 测试如下
  • 在Postgresql 测试如下
  • 总结
• 参考文档

适用范围

any

方案概述

在把ORACLE迁移到PG过程中，我们经常需要转换ORACLE的 DECODE，但是如果将更DECODE(C,NULL,0, C) 进行转换的话，我们可以选择用 CASE WHEN 或 COALESCE 两种方案 ；
但如果是DECODE(C,NULL,0) 进行转换的话，我们就只能选持CASE WHEN 。

实施步骤

在ORACLE 测试如下

#创建模拟数据
 create table test3( a int,b int , c int );
 insert into test3(a,b,c) values(1,1,1);
 insert into test3(a,b) values(2,2);
 insert into test3(a ) values(3);

#原始结果如下：
SQL>   select a,b,c from test3 ; 

         A          B          C
---------- ---------- ----------
         1          1          1
         2          2
         3

#将C例为空的行值替换为0，C例非空行仍为原值 
SQL>   select a,b,DECODE(c, NULL, 0,c) cc  from  test3 ;

         A          B         CC
---------- ---------- ----------
         1          1          1
         2          2          0
         3                     0
#将C例为空的行值替换为0，但原本C例非空的值却变成了空值 
SQL>   select a,b,DECODE(c, NULL, 0) cc  from  test3 ;       

         A          B         CC
---------- ---------- ----------
         1          1
         2          2          0
         3                     0

在Postgresql 测试如下

#创建模拟数据
 create table test3( a int,b int , c int );
 insert into test3(a,b,c) values(1,1,1);
 insert into test3(a,b) values(2,2);
 insert into test3(a ) values(3);

#原始结果如下：
postgres=#   select a,b,c from test3 ; 
 a | b | c 
---+---+---
 1 | 1 | 1
 2 | 2 |  
 3 |   |  
(3 rows)


#通过CASE WHEN 转换，将C例为空的行值替换为0，C例非空行仍为原值 

postgres=#  select  a,b, CASE WHEN c is null  THEN 0  else c END  as cc  from     test3 ;
 select  a,b, coalesce(c, 0) cc   from     test3 ;
 a | b | cc 
---+---+----
 1 | 1 |  1
 2 | 2 |  0
 3 |   |  0
(3 rows)

#通过CASE WHEN 转换，将C例为空的行值替换为0，但原本C例非空的值却变成了空值 
postgres=#  select  a,b, CASE  WHEN c is null  THEN 0  END  as cc  from     test3 ;
 a | b | cc 
---+---+----
 1 | 1 |   
 2 | 2 |  0
 3 |   |  0
(3 rows)

#通过COALESCE ,转换，将C例为空的行值替换为0，C例非空行仍为原值 
postgres=#  select  a,b, coalesce(c, 0) cc   from     test3 ;
 a | b | cc 
---+---+----
 1 | 1 |  1
 2 | 2 |  0
 3 |   |  0
(3 rows)

总结

1. coalesce(c, 0) cc 等价于 CASE WHEN c is null THEN 0 else c END as cc (有else判断)
2. coalesce(c, 0) cc 等价于 ORACLE 中的 有第4个参数 DECODE(c, NULL, 0,c)
3. 而ORACLE 中的只有3个参数 DECODE(c, NULL, 0) 在PG中只能通过 CASE WHEN c is null THEN 0 END as cc 来转换(没有else判断)

参考文档

https://www.postgresql.org/docs/15/functions-conditional.html#FUNCTIONS-CASE
https://www.postgresql.org/docs/15/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL