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
This question is related to
sql
database
postgresql
null
coalesce
The answer 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'
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.
Source: Stackoverflow.com