使用COALESCE在PostgreSQL中处理NULL值 | 使用COALESCE在PostgreSQL中处理NULL值

本文介绍了使用COALESCE在PostgreSQL中处理NULL值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有以下查询

SELECT  DISTINCT
     pt.incentive_marketing,
     pt.incentive_channel,
     pt.incentive_advertising
FROM test.pricing pt
WHERE pt.contract_id = 90000
group by 1,2,3
order by pt.incentive_marketing;

上述查询返回如附图所示的o / p

The above query returns the o/p as shown in the attached image

但我想替换所有空值都为0使用COALESCE
请让我知道如何在上面的SELECT查询中实现

However I want to replace all null values by 0 using COALESCEPlease let me know how this can be achieved in above SELECT query

现在我进一步修改查询合并如下

Now I further modified the query using coalesce as below

SELECT
     COALESCE( pt.incentive_marketing, '0' ),
     COALESCE(pt.incentive_channel,'0'),
     COALESCE( pt.incentive_advertising,'0')
FROM test.pricing pt
WHERE pt.contract_id = 90000
group by 1,2,3

其结果如图2中所示。

我仍然收到一行空白值

推荐答案

您可以使用 COALESCE 用 NULLIF 替换一个简短有效的解决方案：

You can use COALESCE in conjunction with NULLIF for a short, efficient solution:

COALESCE( NULLIF(yourField,'') , '0' )

将返回null，如果yourField为等于第二个值（''在这种情况下），使 COALESCE 函数完全适用于所有情况： / p>

The NULLIF function will return null if yourField is equals to the second value ('' in this case), making the COALESCE function fully working on all cases:

                 QUERY                     |                RESULT
---------------------------------------------------------------------------------
SELECT COALESCE(NULLIF(null  ,''),'0')     |                 '0'
SELECT COALESCE(NULLIF(''    ,''),'0')     |                 '0'
SELECT COALESCE(NULLIF('foo' ,''),'0')     |                 'foo'

这篇关于使用COALESCE在PostgreSQL中处理NULL值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！