Re: [GENERAL] please help me recover from duplicate key in unique index

On Tue, Jan 04, 2000 at 06:47:39PM -0500, Bruce Momjian wrote:
> Try:
>
> SELECT oid, *
> FROM table
> GROUP BY oid
> HAVING count(*) > 1
>
> that should show your duplicates, and you can remove them using
> table.oid = _bad_number_.

ah, i thought each row had a unique oid, and thus, that query wouldn't show the
duplicate content.

i would do something like:

SELECT keyfield FROM tablename GROUP BY keyfield HAVING COUNT(*) > 1;

this will produce a list of rows where keyfield is not unique in tablename.

then, for each of those entries, you want to do a:

SELECT oid, keyfield, other fields FROM tablename WHERE keyfield = <value>;

then you can select which duplicate you want to nuke, and do:

DELETE FROM tablename WHERE OID = 999999;

(i've found that creating an index, non-unique for performance sake) on oid will
improve the preformance of duplicate nukes on really big tables)

mind you, the above process, on a large table is gonna be slow anyways.

--
[ Jim Mercer                 jim@reptiles.org              +1 416 506-0654 ]
[          Reptilian Research -- Longer Life through Colder Blood          ]
[  Don't be fooled by cheap Finnish imitations; BSD is the One True Code.  ]