Re: Table has duplicate keys, what did I do - Mailing list pgsql-general

From Dann Corbit
Subject Re: Table has duplicate keys, what did I do
Date
Msg-id D425483C2C5C9F49B5B7A41F8944154701000C00@postal.corporate.connx.com
Whole thread Raw
In response to Table has duplicate keys, what did I do  (John Gateley <gateley@jriver.com>)
Responses Re: Table has duplicate keys, what did I do
List pgsql-general
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of John Gateley
> Sent: Monday, January 28, 2008 2:04 PM
> To: pgsql-general@postgresql.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@jriver.com>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

pgsql-general by date:

Previous
From: John Gateley
Date:
Subject: Table has duplicate keys, what did I do
Next
From: Jeremy Harris
Date:
Subject: enabling autovacuum