使用COALESCE处理PostgreSQL中的空值

I have the following query

我有以下查询。

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;

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

上面的查询返回o/p，如图所示

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

但是，我想使用COALESCE将所有空值替换为0，请告诉我如何在上面的SELECT查询中实现这一点

Now I further modified the query using coalesce as below

现在，我进一步使用coalesce对查询进行了修改，如下所示

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 

the result of which is as attached in image 2.

其结果如图2所示。

I still receive one row with blank values

我仍然收到一行空值

2 个解决方案

#1

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

您可以结合使用COALESCE与NULLIF来实现一个简短、高效的解决方案:

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

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:

如果您的字段等于第二个值(“在本例中)，那么NULLIF函数将返回null，使COALESCE函数在所有情况下都能工作:

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

#2

If you're using 0 and an empty string '' and null to designate undefined you've got a data problem. Just update the columns and fix your schema.

如果你用0和一个空字符串"和null来表示未定义，就会出现数据问题。只需更新列并修复模式。

UPDATE pt.incentive_channel
SET   pt.incentive_marketing = NULL
WHERE pt.incentive_marketing = '';

UPDATE pt.incentive_channel
SET   pt.incentive_advertising = NULL
WHERE pt.incentive_marketing = '';

UPDATE pt.incentive_channel
SET   pt.incentive_channel = NULL
WHERE pt.incentive_marketing = '';

This will make joining and selecting substantially easier moving forward.

这将使加入和选择更容易。