PostgreSQL import, if constraint not met set null

Question

I'm trying to import a db from file-maker pro into PostgreSQL. As a result of not being maintained well, the data has had some degradation in links between tables over time.
I've attempted to import the data with no constraints and then add the constraint with a USING bock to set it to null if the referenced value doesn't exist.

I have two tables, a people table and a show table. I want to set all the people id's that don't exist to null in the show_leader_id column of the show table. Here's what I have:

BEGIN;
ALTER TABLE show ADD FOREIGN KEY (show_leader_id) REFERENCES people 
    USING (CASE WHEN (SELECT COUNT(*) FROM people WHERE person_id=show_leader_id)=1 THEN show_leader_id ELSE NULL END);
COMMIT;

score 2 · Accepted Answer · edited May 23 '17 at 12:29

Check existence with an EXISTS semi-join:

UPDATE show s
SET    show_leader_id = NULL
WHERE  NOT EXISTS (SELECT 1 FROM people WHERE person_id = s.show_leader_id);

Then add your fk constraint.
If you have concurrent write operations, run both in the same transaction like @Eelke advises. (But that's probably not the case in your situation.)

NOT IN can be treacherous if there are NULL values in people.person_id. Since you are dealing with a mess, this is not unlikely. Details:

PostgreSQL import, if constraint not met set null

1 Answers1