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/