PostgreSQL: Exclude null value from comparison across columns

Question

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?

What's the desired result if all but one columns are NULL, or all columns are NULL? Is the question about exactly 4 value columns or an arbitrary number of columns? — Erwin Brandstetter
– Erwin Brandstetter, Commented Jun 15, 2022 at 21:19

score 6 · Accepted Answer · 2022-06-15 21:10:19Z

6

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

edited Jun 15, 2022 at 21:10

answered Jun 15, 2022 at 21:04

user330315

Sign up to request clarification or add additional context in comments.

Comments

Erwin Brandstetter · Accepted Answer · 2022-06-15 21:39:27Z

Simple boolean logic - looks a bit awkward, but I bet it's the fastest way:

SELECT *
     , COALESCE(NOT (col1 <> col2 OR col1 <> col3 OR col1 <> col4
                  OR col2 <> col3 OR col2 <> col4
                  OR col3 <> col4), true) AS is_a_match
FROM   tbl;

Any non-null, non-equal pair makes the core expression true. Invert with NOT, default to true with COALESCE.

Or closer to your original attempt, which wasn't far off:

SELECT *
     , CASE WHEN (col1 <> col2 OR col1 <> col3 OR col1 <> col4
               OR col2 <> col3 OR col2 <> col4
               OR col3 <> col4)
            THEN false
            ELSE true END is_a_match
FROM   tbl;

db<>fiddle here

Either way, the point is to invert the logic: To ignore comparison to NULL values, look for mismatches (which is only true for two not-null values), not for matches.

The code bloats with more columns. But for a hand full of columns, walking through all pairs is fastest.

Mike Organek · Accepted Answer · 2022-06-15 21:07:32Z

1

If the number of columns is variable, flipping into and out of jsonb could save you some cutting and pasting:

with pivots as (
  select d.id, c.k, c.v
    from de_data d
         cross join lateral jsonb_each_text(to_jsonb(d)) as c(k,v)
   where c.k != 'id'
     and c.v is not null
), match_check as (
  select id, count(distinct v) = 1 as is_a_match 
    from pivots 
   group by id
)
select d.*, mc.is_a_match
  from de_data d
       left join match_check mc on mc.id = d.id;

db<>fiddle here

answered Jun 15, 2022 at 21:07

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: Exclude null value from comparison across columns

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related