Re: How to idenity duplicate rows

David Inglis wrote:
> Can anybody assist with this problem I have a table that has some
> duplicated rows of data, I want to place a unique constraint on the
> columns userid and procno to eliminate this problem in the future but
> how do I identify and get rid of the existing duplication.

To find them, something like

SELECT a, b, c FROM table GROUP BY a, b, c HAVING count(*) > 1;

comes to mind, where you have to list all columns of the table in place
of a, b, c.

As for deleting all but one row in a duplicated group, you're going to
have to get at them by the oid or ctid columns perhaps.

The other idea is to run CREATE TABLE newtable AS SELECT DISTINCT * FROM
oldtable;.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/