Re: Delete all records NOT referenced by Foreign Keys

On Sat, Dec 13, 2003 at 02:20:15 -0600,
"D. Dante Lorenso" <dante(at)lorenso(dot)com> wrote:
> I'd like to run a clean up command on my tables to
> eliminate rows that I'm no longer using in the database.
>
> I want to do something like this:
>
> DELETE FROM tablename
> WHERE IS_REFERENCED_BY_FOREIGN_KEY IS FALSE;
>
> Does anyone know how something like this could be done
> in PostgreSQL? I know I can search all the tables that
> I know refer to this table and see if my primary key
> exists, but I want a solution that does not require me to
> rewrite my code every time a new foreign key constraint
> is added to the database.
>
> There must be a way to ask PostgreSQL for a reference count
> on a given row or something.

If you are more concerned about flexibility than speed you can do something
like the following:

Set all of your foreign key references to the desired table to use an
on delete restrict clause.

Have your application read all of the key values from the desired table
and for each key issue a delete of that key in its own transaction.
This will fail for keys that are referenced (because of the restrict clause).

A more complicated, less future proof, but more efficient approach would
be to have your application find out which tables have references to the
table of interest by looking at the system catalog and then write a
delete query using appropiate where not exist clauses.