Locate multiple duplicate columns in PostgreSQL table
Look for the model - does you need something like this?

fiddle




create table test (id int, value1 int, value2 int)



✓






insert into test values
(1,11,21),
(2,12,22),
(3,13,23),
(4,14,24),
(5,12,24),
(6,16,26),
(7,17,24),
(8,18,28)



8 rows affected






select t1.id id, 
       t2.id dup_id,
       case when t1.value1 = t2.value1 then 'value 1'
            when t1.value2 = t2.value2 then 'value 2'
            else 'some error'
            end dup_field,
       case when t1.value1 = t2.value1 then t1.value1 :: text
            when t1.value2 = t2.value2 then t1.value2 :: text
            else 'some error'
            end dup_value
from test t1, test t2
where t1.id &lt; t2.id
and ( t1.value1 = t2.value1
      or
      t1.value2 = t2.value2 )



id | dup_id | dup_field | dup_value
-: | -----: | :-------- | :--------
 2 |      5 | value 1   | 12       
 4 |      5 | value 2   | 24       
 4 |      7 | value 2   | 24       
 5 |      7 | value 2   | 24