Return zero if no record is found

Question

I have a query inside a stored procedure that sums some values inside a table:

SELECT SUM(columnA) FROM my_table WHERE columnB = 1 INTO res;

After this select I subtract res value with an integer retrieved by another query and return the result. If WHERE clause is verified, all works fine. But if it's not, all my function returns is an empty column (maybe because I try to subtract a integer with an empty value).

How can I make my query return zero if the WHERE clause is not satisfied?

Solution 1

You could:

SELECT COALESCE(SUM(columnA), 0) FROM my_table WHERE columnB = 1
INTO res;

This happens to work, because your query has an aggregate function and consequently always returns a row, even if nothing is found in the underlying table.

Plain queries without aggregate would return no row in such a case. COALESCE would never be called and couldn't save you. While dealing with a single column we can wrap the whole query instead:

SELECT COALESCE( (SELECT columnA FROM my_table WHERE ID = 1), 0)
INTO res;

Works for your original query as well:

SELECT COALESCE( (SELECT SUM(columnA) FROM my_table WHERE columnB = 1), 0)
INTO res;

More about COALESCE() in the manual.
More about aggregate functions in the manual.
More alternatives in this later post:

• How to return a value from a function if no value is found

Solution 2

I'm not familiar with postgresql, but in SQL Server or Oracle, using a subquery would work like below (in Oracle, the SELECT 0 would be SELECT 0 FROM DUAL)

SELECT SUM(sub.value)
FROM
( 
  SELECT SUM(columnA) as value FROM my_table
  WHERE columnB = 1
  UNION
  SELECT 0 as value
) sub

Maybe this would work for postgresql too?

Solution 3

You can also try: (I tried this and it worked for me)

SELECT ISNULL((SELECT SUM(columnA) FROM my_table WHERE columnB = 1),0)) INTO res;