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.