How can I find if any value exists more than once in one row? An example:
id | c1 | c2 | c3
----+----+----+----
1 | a | b | c
2 | a | a | b
3 | b | b | b
The query should return rows 2 and 3 since they have the same value more than once. The solution I'm looking for is not 'where c1 = c2 or c1 = c3 or c2 = c3' since there can be any number of columns in tables I need to test. All values are text but can be any length.
One way to do that is to convert the columns to rows:
select *
from the_table tt
where exists (select 1
from ( values (c1), (c2), (c3) ) as t(v)
group by v
having count(*) > 1)
If you want a dynamic solution where you don't have to list each column, you can do that by converting the row to a JSON value:
select *
from the_table tt
where exists (select 1
from jsonb_each_text(to_jsonb(tt)) as j(k,v)
group by v
having count(*) > 1)
