Using COALESCE to handle NULL values in PostgreSQL

Question

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

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

Answer 1

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'