Thread: Find NOT NULLs in a group of 20 columns

I'm struggling to find anything other than a complex stored proc to do this:

Out of a group of 20 columns return the rows in which:

1. Only 1 of the 20 columns IS NOT NULL
2. More than 1 of the 20 columns IS NOT NULL

Any ideas?

gvim

gvim <gvimrc@gmail.com> writes:
> I'm struggling to find anything other than a complex stored proc to do this:
> Out of a group of 20 columns return the rows in which:

> 1. Only 1 of the 20 columns IS NOT NULL
> 2. More than 1 of the 20 columns IS NOT NULL

> Any ideas?

Cast to int and add up?

SELECT ... WHERE((col1 is not null)::int + (col2 is not null)::int + ... (col20 is not null)::int) = 1     -- or > 1
        regards, tom lane