sql - 从 postgres 的表中选择所有重复项

Question

有没有一种简单的方法可以从 Postgres 表中选择所有重复项？不需要连接或任何花哨的东西。我在堆栈上找到的所有内容都与涉及表上多个字段的连接和重复有关。

我只需要select * from table where table contains duplicate entries
有任何想法吗？

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

像这样的东西可能就是你要找的东西。

  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;