Re: Table has duplicate keys, what did I do

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of John Gateley
> Sent: Monday, January 28, 2008 2:04 PM
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] Table has duplicate keys, what did I do
>
> Somehow I have managed to have two tables with duplicate keys.
> In both tables, the key is an integer, filled from a sequence.
> There is only 1 duplicated entry in each table: in the first
> table, there are two ID "1"s, and in the second table there are
> two ID "123456"s (the second table entry is linked to the first
> table's ID 1).

Because of the nature of the values of the id's (1 and 123456) it sounds
very much like a manual insertion. Is there a unique index on the
column? It definitely sounds like there should be. At any rate, I
guess that someone manually inserted the data. Without a unique index
on the column, there is no protection against this.

> I noticed this because a pg_dump followed by a psql < dumpfile
> will not reload.
>
> I've figured out a fix: a script that cleans the dump file, removing
> the two duplicate lines (leaving the original).
>
> But, mostly, I'm wondering how I managed to get in this state,
> if it was something I did, or perhaps caused by killing the
> postmaster the wrong way (I don't think I ever did this, but
> maybe), or a crash.
>
> I did do a brief search, didn't find anything seemingly related to
this.
>
> Thanks,
>
> j
> --
> John Gateley <gateley(at)jriver(dot)com>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster