Automatically dropping and creating constraints

I do this fairly often, but after talking to some other people I realized it might be a good idea to share a couple of quick SQL scripts. The idea is: you have some large operations you are doing on your database. Of the kind where you load or update lots of data - which means that it will run a lot faster with the constraints in the database turned off. All of them, or maybe just the FOREIGN KEYs, depending on exactly what you are doing. The following simple queries will generate SQL scripts that drop all your constraints, and then re-create them. Adding filters for just a single table/namespace/constrainttype is trivial, but left as an exercise for the reader...

To generate a script to drop constraints:


SELECT 'ALTER TABLE "'||nspname||'"."'||relname||'" DROP CONSTRAINT "'||conname||'";'
 FROM pg_constraint 
 INNER JOIN pg_class ON conrelid=pg_class.oid 
 INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace 
 ORDER BY CASE WHEN contype='f' THEN 0 ELSE 1 END,contype,nspname,relname,conname

Note that the order is important - we must drop FOREIGN KEYs before we drop PRIMARY KEYs and UNIQUE constraints, since they depend on each other.

To generate a script to reload the constraints:


SELECT 'ALTER TABLE "'||nspname||'"."'||relname||'" ADD CONSTRAINT "'||conname||'" '||
   pg_get_constraintdef(pg_constraint.oid)||';'
 FROM pg_constraint
 INNER JOIN pg_class ON conrelid=pg_class.oid
 INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace
 ORDER BY CASE WHEN contype='f' THEN 0 ELSE 1 END DESC,contype DESC,nspname DESC,relname DESC,conname DESC;

Again, note the reversed order that is necessary so we create PRIMARY KEYs and UNIQUE constraints before we create the FOREIGN KEYs.

The easiest way to turn these commands into proper scripts is to just use the psql output feature:


mydb=# \t
Showing only tuples.
mydb=# \o drop_constraints.sql
mydb=# SELECT ...
mydb=# \o create_constraints.sql
mydb=# SELECT ...

You can then just load those files as include files in psql. Obviously, you need to script both the DROP and CREATE statements before you actually drop something.

And the second obvious note - always run these things in a transaction. Take advantage of the transactional DDL in PostgreSQL - as always. I often run the whole thing - drop, bulk work, recreat - inside a single transaction. That way, applications may not even need to know there is service happening...

Finally, in some databases you may need to add further restrictions, and always verify the scripts before you run them :)


Comments

Just a warning that if you pipe these two commands to two separate scripts, and then try to run those scripts within a transaction, that \i in psql will cause implicit commits.

Posted on Dec 20, 2008 at 01:38 by Robert Treat.

Not sure what you mean. AFAIK, it should only do implicit commit if you're running psql in single transaction mode?

Posted on Dec 31, 2008 at 14:17 by Magnus.

There's a major "flaw" in this thing.
As soon as you drop the contraints (1st query) you won't be able to reload them (2nd one). Infact, once you ALTER a TABLE to DROP a CONSTRAINT of its, that constraint won't show up anymore in the pg_constraint system table, thus making the 2nd query useless! So you need to save some data from pg_constraint into a table for later restoration. Nonetheless, this post is very interesting.

Posted on Jan 1, 2009 at 09:37 by Vincenzo Romano.

Yeah, that's why I said "Obviously, you need to script both the DROP and CREATE statements before you actually drop something."

The easiest way is to simply save the script...

But it's certainly true that if you run the script generated by the first query, the system tables will be empty of the information, and there is no way to recover it (other than backups).

Posted on Jan 1, 2009 at 11:42 by Magnus.

What I prefer in this case is to create some extra structure (a table and a couple of functions) to make the bookkeeping of constraints. What actually happens in my experience is that the application needs to do some bulk load. In that case the pre-generated scripts don't fit well and need some maintenance, while the table+functions solution is "automatic". In any case your posting shows a hands-on view of some system tables, which is always a good thing! Thanks.

Posted on Jan 2, 2009 at 09:33 by Vincenzo Romano.

How about indexes as well?

SELECT 'DROP INDEX "'||nspname||'"."'||relname||'" RESTRICT;' FROM pg_index INNER JOIN pg_class ON indexrelid=pg_class.oid INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace WHERE indisprimary=FALSE and indisvalid=TRUE AND nspname NOT LIKE 'pg_%25' ORDER BY nspname,relname;

SET search_path=public; SELECT pg_get_indexdef(pg_index.indexrelid)||';' FROM pg_index INNER JOIN pg_class ON indexrelid=pg_class.oid INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace WHERE indisprimary=FALSE and indisvalid=TRUE AND nspname NOT LIKE 'pg_%25' ORDER BY nspname,relname;

Posted on Jan 5, 2009 at 17:32 by Glyn Astill.

THANK YOU. I don't know why this functionatliy isn't in Postgresql in the first place, seeing as how apparently Oracle and MySQL have it.

Posted on Mar 10, 2011 at 21:26 by Gavin.

Add comment

New comments can no longer be posted on this entry.

Conferences

I speak at and organize conferences around Open Source in general and PostgreSQL in particular.

Upcoming

PGDay Chicago 2024
Apr 26, 2024
Chicago, USA
PGConf.DEV 2024
May 28-31, 2024
Vancouver, Canada

Past

SCaLE 2024
Mar 14-17, 2024
Pasadena, USA
Nordic PGDay 2024
Mar 12, 2024
Oslo, Norway
FOSDEM PGDay 2024
Feb 2-4, 2024
Brussels, Belgium
PGConf.EU 2023
Dec 12-15, 2023
Prague, Czechia
PGConf.NYC 2023
Oct 3-5, 2023
New York, USA
More past conferences