Re: Table has duplicate keys, what did I do

for now, are you able to insert duplicate keys(primary-key) into the two
tables you mentioned? if you can, check if your index is valid or not.
if index is valid, check if the unique contraint is still valid or not
-- perhaps you turned off the unique constraint, and insert the dup key,
and didn't turn back on the unique constrain validation...

JF

-----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:26 PM
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Table has duplicate keys, what did I do

On Mon, 28 Jan 2008 14:11:21 -0800
"Dann Corbit" <DCorbit(at)connx(dot)com> wrote:

> > -----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.

Yes, the id 1 definitely indicates to me that I did something.
However, there is an index on the column: it's the primary key for the
table. I'm not sure how I could manually insert it if there were an
existing index, or later create the index if it didn't exist when I did
the insert.

Thanks,

j

--
John Gateley <gateley(at)jriver(dot)com>

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend