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?

Theodicy

INTO var appended to SELECT ... is only valid in PL/pgSQL code, not in plain SQL. I assume this is part of a PL/pgSQL function or DO statement. Correct? – Upchurch

Answer 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

Upchurch

@FreshPrinceOfSO: I think it should be noted that COALESCE is the SQL standard way to do it. – Upchurch

I had a bad experience with COALESCE... Let's just say we're not friends anymore. – Derogatory

@FreshPrinceOfSO It is more of I had a bad experience with SQL Server then I had a bad experience with COALESCE. There is nothing wrong with COALESCE in other RDBMS. – Phatic

Answer 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?

Pyne

Answer 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;

Longinus

Answer 4

You can use exists clause.

IF EXISTS(SELECT  FROM my_table WHERE columnB = 1)
      THEN
         res := SUM(columnA);
      ELSE
         res := 0
      END IF;

Cassowary

Answer 5

COALESCE should do the trick, for example :

(SELECT columnA from my_table where columnB=123) - 
COALESCE((SELECT SUM(columnA) FROM my_table WHERE columnB = 1 INTO res),0)

Here, if the second select query returns no rows, it defaults to 0.

Furrow