Today I Learned

hashrocket A Hashrocket project

SQL Truncate

Today during a smoke-test I watched a SQL-savvy colleague wipe a database table with the truncate syntax.

truncate users;

truncate is a feature of Postgres and is included in the SQL:2008 standard.

truncate includes a cascade option that further truncates all tables that have foreign-key references to any of the named tables, or any tables included by cascade itself. That sounds either useful or extremely destructive, depending on context.

This replaces my go-to for this type of cleanup, the Rails console:

User.delete_all

The benefits of truncate over delete_all are that you have more control and it's probably a little faster due to requiring one less layer of abstraction.

delete_all & truncate

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.