Using COALESCE to handle NULL values in PostgreSQL
SqlDatabasePostgresqlNullCoalesceSql Problem Overview
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
Sql Solutions
Solution 1 - Sql
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'
Solution 2 - Sql
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.