Re: Removing records that violate foreign key

On Tue, 18 Jun 2002, Cliff Wells wrote:

> Hello,
>
> I'm porting an application's data from a proprietary format into PostgreSQL
> 7.2.1.  I'm using ecpg and I basically follow this process:
>
> create tables
> create indexes
> import data
> add foreign key constraints
>
> I have to add the foreign key constraint last as otherwise the import will fail
> as the records being referenced won't necessarily exist in PostgreSQL yet.  The
> problem is that now I'd like to delete any records that violate the constraint.
>  Any suggestions on a quick way to do this?


For match full maybe something like:
 delete from fk where not exists (
  select * from pk where pk.pkkeycol1=fk.fkkeycol1 [and ...]
 );

For the unspecified match type, I think you need to add
 fk.fkkeycol1 is not null [and ...]
clauses to the top level where.