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 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

Have you tried to replace null values with zeroes using `COALESCE`? What exactly went wrong? — Evgeniy Chekan, Dec 15 '14 at 07:30
Then please try the coalesce() function and report any problem you have with it. Read the manual if you are unsure about how to use it: http://www.postgresql.org/docs/current/static/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL - "*No I haven't tried*" isn't the way SO works. — , Dec 15 '14 at 07:52
Additionally: using `distinct` **and** `group by` doesn't make sense. `group by 1,2,3` already makes all columns distinct. So does the `distinct` operator. — , Dec 15 '14 at 07:54
@a_horse_with_no_name I have used the coalesce and eadited the question , please see — ronan, Dec 15 '14 at 08:51
You may have empty strings in addition to null values there - these won't be altered by coalesce(), you need to use some "case" statement. Why your numbers are strings anyway? — Arvo, Dec 15 '14 at 09:00
Are you sure you're seeing NULL's? Or is it an empty string '' your seeing? Change pgAdmin-settings to show where you have a NULL, instead of an empty field that could be NULL or ''. — Frank Heikens, Dec 15 '14 at 09:16

Andrea Ligios · Accepted Answer · 2020-09-29T10:55:31.340

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'

score 8 · Answer 2 · answered Sep 13 '17 at 03:22

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.

Using COALESCE to handle NULL values in PostgreSQL

2 Answers2

Linked