CODEWITHSUNDEEP
sqlpostgresql
Codegator
Codegator

Reputation: 637

SQL/Postgresql: Find duplicates based on all the columns in the table

I have to find duplicates in the table based on all the columns. I know the below query to identify the duplicates based on multiple or single column

select count(*), id, country
from idp.Country_Table
group by id, country
having count(*) > 1

but is there way where we can do it based on all the columns of tables without specifying the columns names? I have 156 columns in table so specifying each column name in the query would be pain.

Upvotes: 0

Views: 161

Answers (2)

Vivek Puurkayastha
Vivek Puurkayastha

Reputation: 536

I know I am Late to the Party. But although effective, problem with @Gordon Linoff answer is that it does not allow to order by a particular column if needed.

We can achieve the same as below, which also allows to order by a column:

WITH duplicate_marked AS
(
  SELECT 
     *
     , ROW_NUMBER () over(PARTITION BY ct.* ORDER BY col_1 ) AS serial
  FROM idp.Country_Table AS ct
)
SELECT * FROM duplicate_marked
WHERE serial > 1 -- select the duplicates only
;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

In Postgres, you can treat the record as an "item":

select ct, count(*)
from idp.Country_Table ct
group by ct
having count(*) > 1;

Upvotes: 2

Related Questions