Exclude rows with null values

Question

I'm doing a bunch of sum queries:

SELECT col1 + col2 + col3 + ...

Some of the values in some of the columns are null. I'm checking for them by doing:

SELECT CASE WHEN col1 is not null and col2 is not null and ...

I wonder if there is a more concise syntax for accomplishing this task.

Unite

Answer 1

Well, since the sum of any number and null is null, you can do something like the following (with the obvious ... filled in):

select big_honking_sum
from(
    select col1+col2+col3+...+coln as big_honking_sum
    from some_table
)sum
where big_honking_sum is not null;

Scoot

Ohh, thanks. I had it in my head that 1 + null equaled 1. I guess I've been programming too much javascript. – Unite

@Unite - You're most welcome. If you found my answer helpful, please consider accepting it by clicking on the check mark below the upvote/downvote arrows next to my answer. – Scoot

;) SO won't let me select an answer for the first fifteen minutes. – Unite

Answer 2

Use COALESCE function if you can accept that a NULL value will be treated as 0.

SELECT COALESCE(Col1,0)
      + COALESCE(Col2,0)
      + COALESCE(Col3,0)
      AS TOTAL FROM table;

Perhaps you could consider using 0 as default value instead of NULL if applicable.

Grey

Thanks for the advice. In my case, null is for unspecified values, where zero is for zero values. – Unite

Answer 3

Much simpler, faster, clearer:

... WHERE (col1, col2, col3) IS NOT NULL;

The expression returns true if (and only if) all fields in the ROW expression are NOT NULL. See:

• Why is IS NOT NULL false when checking a row type?

Can be applied to the whole row, too:

SELECT ...
FROM   my_table t
WHERE  (t.*) IS NOT NULL;

Postgres even has a dedicated function for the purpose: num_nulls(). Slightly more expensive in my tests on Postgres 15, but clearer:

... WHERE num_nulls(col1, col2, col3) = 0;

fiddle

Illailladvised