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

Using COALESCE to handle NULL values in PostgreSQL

The Solution to Using COALESCE to handle NULL values in PostgreSQL is

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'

~ Answered on 2014-12-15 13:56:57

Using COALESCE to handle NULL values in PostgreSQL

The Solution to Using COALESCE to handle NULL values in PostgreSQL is

Most Viewed Questions: