CODEWITHSUNDEEP
postgresqlduplicates
tok
tok

Reputation: 981

Postgres: how to find rows having duplicate values in fields

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.

Upvotes: 0

Views: 227

Answers (1)

user330315
user330315

Reputation:

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)

Online example

Upvotes: 1

Related Questions