select all duplicates from a table in postgres

Question

Is there a simple way to select all duplicates from a Postgres table? No need for joins or anything fancy. Everything I find on stack is about joins and duplicates involving multiple fields on a table.

I just need to select * from table where table contains duplicate entries

Any ideas?

Table definition from Postgres:

scotchbox=# \d+ eve_online_market_groups

                                                           Table "public.eve_online_market_groups"
   Column   |              Type              |                               Modifiers                               | Storage  | Stats target | Description 
------------+--------------------------------+-----------------------------------------------------------------------+----------+--------------+-------------
 id         | integer                        | not null default nextval('eve_online_market_groups_id_seq'::regclass) | plain    |              | 
 name       | character varying(255)         | not null                                                              | extended |              | 
 item_id    | integer                        | not null                                                              | plain    |              | 
 slug       | character varying(255)         | not null                                                              | extended |              | 
 created_at | timestamp(0) without time zone |                                                                       | plain    |              | 
 updated_at | timestamp(0) without time zone |                                                                       | plain    |              | 
Indexes:
    "eve_online_market_groups_pkey" PRIMARY KEY, btree (id)
Has OIDs: no

Not sure what you mean by fancy, but you would need to join (at least once) to itself, to find duplicates. — Robins Tharakan
– Robins Tharakan, Commented Feb 17, 2016 at 0:30
Do you have what the sql would look like for a sample table to find duplicates? Your answer doesn't help me any — TheWebs
– TheWebs, Commented Feb 17, 2016 at 0:31
There can't be an answer at this stage, because the question needs to provide at least a basic SQL table, with column names, structure for any meaningful (working) solution. — Robins Tharakan
– Robins Tharakan, Commented Feb 17, 2016 at 0:32
There are two ways to find out duplicates: 1) select field1, field2,... from table group by field1, field2,... having count(*) > 1 to select duplicating combinations; 2) select * from (select *, count(*) over (partition by field1, field2,...) as dup_cnt from table) t where dup_cnt > 1 to select all columns. — Abelisto
– Abelisto, Commented Feb 17, 2016 at 0:54

messanjah · Accepted Answer · 2016-02-17 01:06:05Z

14

Something like this might be what you're looking for.

  SELECT columns_that_define_duplicates -- SELECT item_id, name, slug perhaps?
       , count(*)
    FROM eve_online_market_groups
GROUP BY columns_that_define_duplicates
  HAVING count(*) > 1;

answered Feb 17, 2016 at 1:06

messanjah

9,3084 gold badges30 silver badges42 bronze badges

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

Collectives™ on Stack Overflow

select all duplicates from a table in postgres

Table definition from Postgres:

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Table definition from Postgres:

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related