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

Answer 1

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;