Postgresql turn null into zero

Handling those pesky nulls

Whether it's financial records or inventory management, numeric nulls create unnecessary confusion. The workaround? COALESCE to the rescue:

SELECT COALESCE(SUM(sales), 0) FROM quarterly_sales; -- Even no sales translate to zero, not null. Ouch, right?


Applicable scenarios

• The 0 in financial databases where no revenue means…no revenue.
• Stock-take situations where a null could represent an empty shelf.
• Performance metrics where leaving out 0 could mess with your data interpretation and visualisation.

Being NULL intolerant ensures you interpret your data correctly.

When null just won't cut it

Got non-numeric columns? Use COALESCE to set default values:

SELECT COALESCE(address, 'Not provided') FROM user_profiles; -- Because saying 'Not provided' is more polite than <null>!


Always show meaningful defaults, throw nulls out the door!

'CASE' closed with COALESCE

COALESCE can be compared to a simplified form of the CASE expression, swiftly jumping over NULLs:

COALESCE(value1, value2, ..., valueN) -- For every null, give me the first available non-null value 🎁.


It's equivalent to:

CASE
  WHEN value1 IS NOT NULL THEN value1 -- The first hero arrives, value1!
  WHEN value2 IS NOT NULL THEN value2 -- Need not worry if value1 failed, there’s value2!
  ...
  ELSE valueN -- When all else fails, there's always valueN!
END


On paper, all COALESCE does is return the first non-null value. But in practice, it's a valuable tool in a data scientist's arsenal.

Common pitfalls

• Overlooking underlying problems eluded by the null.
• Overusing until you inevitably end up ignoring data quality issues.
• Failing to acknowledge that COALESCE can return null if every single argument is null.

Pro Tip: Use COALESCE responsibly 😅.

Reveal the might of COALESCE

Imagine default values in calculations, or handling max value lookups:

SELECT COALESCE(MAX(revenue), 0) FROM monthly_sales;


Complex calculations

COALESCE helps simplify:

• Aggregate functions by avoiding nulls that distort summations or averages.
• Join operations by filling in the gaps when joining tables with potential nulls.
• Date processing by providing alternatives for incomplete or missing date information.

Choose your defaults wisely

Here's a trick! Provide conditional default values or custom messages:

SELECT COALESCE(email, 'Email not set', 'N/A') FROM customer_contacts; -- For those elusive customers who prefer smoke signals over emails! 😉
