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;

Community · Accepted Answer · 2017-05-23 12:29:26Z

2

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:

edited May 23, 2017 at 12:29

CommunityBot

11 silver badge

answered Jul 1, 2014 at 18:59

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL import, if constraint not met set null

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related