Re: Finding orphan records

From: "Jonel Rienton" <jonel(at)rientongroup(dot)com>
To: "'Wes'" <wespvp(at)syntegra(dot)com>
Cc: "'Postgresql-General'" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Finding orphan records
Date: 2006-01-12 06:23:02
Message-ID: 000401c61740$a4dfe440$0302a8c0@aspire
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Resending sample query, darn where clause didn't wrap

select a.*,b.* from a
left outer join b on a.id = b.a_id
where b.id is null;

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Wes
Sent: Wednesday, January 11, 2006 11:51 PM
To: Postgresql-General
Subject: [GENERAL] Finding orphan records

I'm trying to find/delete all records in table A that are no longer
referenced by tables B or C. There are about 4 million records in table A,
and several hundred million in tables B and C.

Is there something more efficient than:

select address_key, address from addresses where ( not exists(select 1 from
B where BField=addresses.address_key limit 1) ) and ( not exists(select 1
from C where CField=addresses.address_key limit 1) )

Of course, all fields above are indexed.

There are foreign key references in B and C to A. Is there some way to
safely leverage that?

Wes

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.14.16/225 - Release Date: 1/9/2006

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Wes 2006-01-12 06:43:30 Re: Finding orphan records
Previous Message Jonel Rienton 2006-01-12 06:17:52 Re: Finding orphan records