Re: Finding orphan records - Mailing list pgsql-general

From Jonel Rienton
Subject Re: Finding orphan records
Date
Msg-id 000301c6173f$ebf10ea0$0302a8c0@aspire
Whole thread Raw
In response to Finding orphan records  (Wes <wespvp@syntegra.com>)
List pgsql-general
I would use an outer join and check only those null-value records in the
right table with id's referencing table A

Sample query:

select a.*,b.* from a
left outer join b on a.id = b.a_id  -- assuming a_id is my referencing
column to a
where b.id is null;

This will yield all columns in table a which has a null value on table b

This is just from the top of my head, just a concept, I might have some
syntax error.

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.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



pgsql-general by date:

Previous
From: "Joshua D. Drake"
Date:
Subject: Re: Granting Privleges on All Tables in One Comand?
Next
From: "Jonel Rienton"
Date:
Subject: Re: Finding orphan records