SQL 使用 COALESCE 处理 PostgreSQL 中的 NULL 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27479180/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using COALESCE to handle NULL values in PostgreSQL
提问by ronan
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 COALESCEPlease 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
我仍然收到一行空白值
回答by Andrea Ligios
You can use COALESCEin conjunction with NULLIFfor a short, efficient solution:
您可以COALESCE与 结合使用NULLIF以获得简短有效的解决方案：
COALESCE( NULLIF(yourField,'') , '0' )
The NULLIFfunctionwill return null if yourField is equals to the second value (''in this case), making the COALESCEfunction fully working on all cases:
NULLIF如果 yourField 等于第二个值（''在本例中），该函数将返回 null ，从而使该COALESCE函数在所有情况下都能正常工作：
QUERY | RESULT
---------------------------------------------------------------------------------
SELECT COALESCE(NULLIF(null ,''),'0') | '0'
SELECT COALESCE(NULLIF('' ,''),'0') | '0'
SELECT COALESCE(NULLIF('foo' ,''),'0') | 'foo'
回答by Evan Carroll
If you're using 0and an empty string ''and nullto 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.
这将使加入和选择变得更加容易。
