PostgreSQL: Exclude null value from comparison across columns

Imagine this table:

id   col1    col2    col3    col4
1    A       A       C
2            B       B       B
3    D               D

I would like to add a column that tells me if all not-null values of the row match.

So the ideal output is:

id   col1    col2    col3    col4   is_a_match
1    A       A       C              FALSE
2            B       B       B      TRUE
3    D               D              TRUE

I have tried:

select *,
     case  
         when col1 = col2
                and col2 = col3
                and col3 = col4
         then 'TRUE'
         else 'FALSE'
     end as is_a_match
from my_table

But would return false for all due to the null values.

What is the best way to achieve the output above?

Solution

You could transform the columns to rows, the count the distinct values. That will automatically ignore NULL values:

select t.*,
       (select count(distinct x.col)
        from (
          values (t.col1), 
                 (t.col2), 
                 (t.col3), 
                 (t.col4)
        ) as x(col)
        where x.col is not null) = 1 as is_a_match
from the_table t

If you don't want to list all columns manually, you can use some JSON magic to turn the columns to rows in order to count the distinct values:

select t.*, 
       (select count(distinct x.val)
        from jsonb_each_text(to_jsonb(t) - 'id') as x(col, val)
        where x.val is not null) = 1
from the_table t