74

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

9
  • 4
    Have you tried to replace null values with zeroes using COALESCE? What exactly went wrong? Commented Dec 15, 2014 at 7:30
  • 2
    no I havent tried , please show me using coalesce
    – ronan
    Commented Dec 15, 2014 at 7:44
  • 4
    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: postgresql.org/docs/current/static/… - "No I haven't tried" isn't the way SO works.
    – user330315
    Commented Dec 15, 2014 at 7:52
  • 4
    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.
    – user330315
    Commented Dec 15, 2014 at 7:54
  • 6
    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
    Commented Dec 15, 2014 at 9:00

2 Answers 2

172

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'
0
8

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.