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:
TRUNCATE TABLE orders;
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:
TUNCATE TABLE orders CASCADE;
You'll get a notice of the form:
NOTICE: truncate cascades to table "order_items"