I have several large tables (10 million to 200 million rows) that have foreign keys with each other by *convention*, but no actual FOREIGN KEY constraints. Over the course of years, orphaned records (children with no parent) have accumulated and now I want to clean them up. I can't just create the FK constraint because the orphans cause violations. Executing something like the following statement would work, but even with indexes it takes an insane amount of time to execute for each of the tables: DELETE FROM child_table WHERE parentid NOT IN (SELECT parentid FROM parent_table) Are there any better ways to accomplish this task? I've brainstormed extensively and searched the list archives to no avail. It goes without saying that once the cleanup is done, FK constraints will be added so this is never a mess again! I am using PostgreSQL 7.4.6 on Linux, although I could restore these tables on an 8.0.3 server if it would make things go faster! Thanks, Roman _____________________________________ Check All Email Accounts Anywhere! Check your POP3 and webmail account from any PC. With no ads http://www.fusemail.com _____________________________________ Consolidate your email! http://www.fusemail.com ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
