Return zero if no record is found
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
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;
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?