DELETE all data really fast with TRUNCATE TABLE CASCADE

Though it is a rare occurrence, we have had occasions where we need to purge ALL data from a table. Our preferred is the TRUNCATE TABLE approach because it's orders of magnitude faster than the DELETE FROM construct. You however can't use TRUNCATE TABLE unqualified, if the table you are truncating has foreign key references from other tables. In comes its extended form, the TRUNCATE TABLE .. CASCADE construct which was introduced in PostgreSQL 8.2, which will not only delete all data from the main table, but will CASCADE to all the referenced tables.

Here is a quick demonstration. Suppose you had two tables orders and order_items where the order_items table references the orders.order_id column. If you do this:


You'll get notice

ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "order_items" references "orders".
HINT:  Truncate table "order_items" at the same time, or use TRUNCATE ... CASCADE.

It doesn't matter if you have CASCADE UPDATE or DELETE rule in place, the CASCADE approach will still be able to purge all data in the referenced tables by changing your statement to:


You'll get a notice of the form:

NOTICE:  truncate cascades to table "order_items"