Today I Learned

hashrocket A Hashrocket project

Truncate Tables With Dependents

In Truncate All Rows, I talked about how postgres's truncate can be used to quickly delete all rows in a table. In practice this alone won't be very useful though, because tables usually have other tables that depend on them via foreign keys. If you have tables A and B where B has a foreign key referencing A, then trying to truncate A will result in something like this:

> truncate A;
ERROR:  cannot truncate a table referenced in a foreign key constraint

Fortunately, truncate has some tricks up its sleeve.

If you know two tables are tied together via a foreign key constraint, you can just truncate both of them at once:

> truncate A, B;
TRUNCATE TABLE;

If many tables are tied together in this way and you are looking to throw all of it out, then a simpler approach is to cascade the truncation:

> truncate A cascade;
NOTICE:  truncate cascades to table "B"
TRUNCATE TABLE

Use these with care and potentially within transactions because your data will go bye bye.

h/t Dillon Hafer and Jack Christensen

See More #sql TILs
Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, our series of free PostgreSQL screencasts.