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