Using COALESCE to handle NULL values in PostgreSQL

SqlDatabasePostgresqlNullCoalesce

Sql Problem Overview


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 enter image description here

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

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 

the result of which is as attached in image 2.

I still receive one row with blank values

Sql Solutions


Solution 1 - Sql

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

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

The NULLIF function will return null if yourField is equal to the second value ('' in the example), 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'

Solution 2 - Sql

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.

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.

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionronanView Question on Stackoverflow
Solution 1 - SqlAndrea LigiosView Answer on Stackoverflow
Solution 2 - SqlEvan CarrollView Answer on Stackoverflow