postgresql - 添加主键 : Table contains duplicated values

Question

在 PostgreSQL 8.4.13 中，我有 2 个表和一个用于填充第二个表的过程:

    create table pref_users (
            id varchar(32) primary key,
            first_name varchar(64),
            last_name varchar(64),
            female boolean,
            avatar varchar(128),
            city varchar(64),
            login timestamp default current_timestamp,
            logout timestamp,
            last_ip inet,
            vip timestamp,
            mail varchar(256)
    );

    create table pref_rep (
            rep_id serial,
            id varchar(32) references pref_users(id) check (id <> author) on delete cascade,
            author varchar(32) references pref_users(id) on delete cascade,
            author_ip inet,
            good boolean,
            fair boolean,
            nice boolean,
            about varchar(256),
            stamp timestamp default current_timestamp
            /* primary key(id, author) */
    );

   create or replace function pref_update_rep(_id varchar,
            _author varchar, _author_ip inet,
            _good boolean, _fair boolean, _nice boolean,
            _about varchar) returns void as $BODY$
            begin

            delete from pref_rep
            where id = _id and
            age(stamp) < interval '1 hour' and
            (author_ip & '255.255.255.0'::inet) =
            (_author_ip & '255.255.255.0'::inet);

            update pref_rep set
                author    = _author,
                author_ip = _author_ip,
                good      = _good,
                fair      = _fair,
                nice      = _nice,
                about     = _about,
                stamp     = current_timestamp
            where id = _id and author = _author;

            if not found then
                    insert into pref_rep(id, author, author_ip, good, fair, nice, about)
                    values (_id, _author, _author_ip, _good, _fair, _nice, _about);
            end if;
            end;
    $BODY$ language plpgsql;

pref_users 表包含有关用户的一般信息。

pref_rep 包含由其他用户(列 about)创建的关于用户(列 id)的评论(列 author)。

对于第二个表，我忘记声明 primary key 对(该行已在上面注释)。

我试图在 psql 提示符下添加该主键，但它失败了 - 可能是因为某些原因(我不知道我上面的过程怎么会发生这种情况？)我有几个相同的 author 注释的记录多次出现相同的 id:

# alter table pref_rep add primary key(id, author);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "pref_rep_pkey" for table "pref_rep"
ERROR:  could not create unique index "pref_rep_pkey"
DETAIL:  Table contains duplicated values.

我的问题是如何找到重复的 id 和 author 对？

我试过:

# select id, count(id) from pref_rep group by id order by count desc limit 5;
       id       | count
----------------+-------
 OK408547485023 |   706
 OK261593357402 |   582
 DE11198        |   561
 DE13041        |   560
 OK347613386893 |   556
(5 rows)

但这当然不会给我成对...

更新:Catcall 的建议(谢谢!)给了我 190 个这样的重复对:

           id           |         author         | count
------------------------+------------------------+-------
 DE10598                | OK495480409724         |     2
 DE12188                | MR17925810634439466500 |     3
 DE13529                | OK471161192902         |     2
 DE13963                | OK434087948702         |     2
 DE14037                | DE7692                 |     2
......
 VK45132921             | DE3544                 |     2
 VK6152782              | OK261593357402         |     2
 VK72883921             | OK506067284178         |     2
(190 rows)

但实际上我真正的问题是如何删除旧的(stamp 列)重复项？我在 psql 提示符下尝试了很多查询，但均未成功...

Answer 1

这应该可以识别重复项。

select id, author 
from pref_rep
group by id, author
having count(id) > 1

您可能还必须查看 NULL，因为这两个列都允许 NULL。