Thread: Can one turn off constraints/indices temporarily?

I'm sorry if this question is very naive.  I'm very green at this.

When I read the SQL code generated by pg_dump, I notice that all the
constraints and indices are defined after all the data has been
inserted.  I figure this makes the bulk transfers much faster, since
each insert can happen without the need to check constraints and/or
update indices each time.

Now, suppose that one had to insert many records at once.  Is it
possible to mimic the above strategy by turning off the constraints
and indexing temporarily (without dropping them altogether), and
turning them back on after all the inserts have been made.  (Of
course, upon turning it back on, constraints would have to be checked
and indices would need to be updated, therefore one would repay the
"time debt", but hopefully this would still be smaller than doing all
this after each insert.)

Thanks!

kj

<kynn@panix.com> writes:
> Now, suppose that one had to insert many records at once.  Is it
> possible to mimic the above strategy by turning off the constraints
> and indexing temporarily (without dropping them altogether), and
> turning them back on after all the inserts have been made.

No.  You can drop and re-add them if you have a mind to.  People
do this fairly often with indexes and foreign key constraints ---
there's no advantage in doing it for CHECK constraints though.

            regards, tom lane