Postgresql return 0 if returned value is null

Nuances of null value handling

Let's navigate the intriguing world of NULL value management in SQL, starting from its most common scenario: dealing with averages and sums.

COALESCE and AVG: friends or foes?

Suppose you have this messing with your perfect averages:

SELECT AVG(COALESCE(price, 0)) FROM products;


Here, products with a NULL price are treated as 0 contributing to an unfair average. Not exactly fair for expensive Apple products, right? 🍎💰

The harmless tale of SUM and COALESCE

When SUM meets NULL, it’s time for COALESCE to jump in with a saving 0:

SELECT COALESCE(SUM(price), 0) FROM products;


No products sold? No worries, your sum is zero! Investment in invisible products is 0! 💸👻

WHERE vs. NULL: a fair fight

In case you opt to sidestep NULLs altogether:

SELECT AVG(price) FROM products WHERE price IS NOT NULL;


This syntax only includes products with a non-NULL price. Your high-end Macs are now safe from the NULL invasion. 🛡️💻

Decoding COALESCE

Every SQL artisan needs their COALESCE tool for consistent results, even in sparse data situations.

COALESCE swings into joined tables

Given NULL values from a left join, COALESCE saves the day with 'default_value':

SELECT a.id, COALESCE(b.attribute, 'default_value') FROM table_a a
LEFT JOIN table_b b ON a.id = b.foreign_id;


Worried about empty tables joining the party? Next round is on COALESCE! 🍻🥳

COALESCE teams up with CASE

When COALESCE and CASE synchronize, you achieve a higher level of control over defaults:

SELECT id, COALESCE(
    CASE WHEN condition THEN calculation ELSE NULL END,
    default_value
) FROM table;


Who wouldn’t join forces with COALESCE? CASE closed! 🧳🤝

COALESCE masters non-numeric data

COALESCE is a jack of all trades, handling strings and dates with equal aplomb:

SELECT COALESCE(string_column, 'default string') FROM table;
SELECT COALESCE(date_column, CURRENT_DATE) FROM table;


Dates, Strings, Numbers... All walk into a bar. Who do they meet first? COALESCE! 🥂🍺

Common traps: The vanishing zero in average

It's prudent to remember that inserting 0 into AVG calculations for NULL (indicating missing data) can heavily tip the scales, bringing your data average crashing down.

SELECT AVG(price) FROM products WHERE price IS NOT NULL;


Disappearing zeros in averages: Scales turned heavy. The perfect crime scene! 🕵️🎱