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/ ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
