PostgreSQL - disabling constraints

Posted by azp74 on Stack Overflow See other posts from Stack Overflow or by azp74
Published on 2010-04-21T02:04:33Z Indexed on 2010/04/21 2:13 UTC
Read the original article Hit count: 312

Filed under:
|

I have a table with approx 5 million rows which has a fk constraint referencing the primary key of another table (also approx 5 million rows).

I need to delete about 75000 rows from both tables. I know that if I try doing this with the fk constraint enabled it's going to take an unacceptable amount of time.

Coming from an Oracle background my first thought was to disable the constraint, do the delete & then reenable the constraint. PostGres appears to let me disable constraint triggers if I am a super user (I'm not, but I am logging in as the user that owns/created the objects) but that doesn't seem to be quite what I want.

The other option is to drop the constraint and then reinstate it. I'm worried that rebuilding the constraint is going to take ages given the size of my tables.

Any thoughts?

© Stack Overflow or respective owner

Related posts about postgresql

Related posts about constraints