Disclaimer/Disclosure: Some of the content was synthetically produced using various Generative AI (artificial intelligence) tools; so, there may be inaccuracies or misleading information present in the video. Please consider this before relying on the content to make any decisions or take any actions etc. If you still have any concerns, please feel free to write them in a comment. Thank you.
---

Summary: Learn how to efficiently truncate all tables in PostgreSQL using a simple SQL command. Truncating tables is a quick way to delete all data from them while keeping their structure intact, useful for testing or resetting database content.
---

Truncating tables in PostgreSQL can be a handy operation, especially in scenarios where you need to clear out data from multiple tables without deleting the tables themselves. Truncating is faster than deleting rows one by one and also resets any associated sequence generators. Here’s a straightforward way to truncate all tables in PostgreSQL:

[[See Video to Reveal this Text or Code Snippet]]

Let's break down the steps:

DO block: PostgreSQL allows executing anonymous code blocks using the DO keyword. This block encapsulates the procedure for truncating tables.

DECLARE: This section declares variables used within the block. In this case, r is a record variable that will hold table names fetched from the query.

FOR loop: It iterates over the result set returned by the query inside the parentheses. Here, it fetches table names from the pg_tables system catalog view filtered by the current schema.

EXECUTE: Inside the loop, EXECUTE dynamically runs SQL commands. It concatenates the TRUNCATE TABLE statement with the current table name, using quote_ident to properly handle table names with special characters and CASCADE to truncate dependent tables as well.

END LOOP: Marks the end of the loop.

END: Marks the end of the DO block.

Executing this SQL code will truncate all tables within the current schema, including their dependent tables, effectively removing all data while keeping the table structures intact.

It's crucial to exercise caution when truncating tables, especially in a production environment, as this operation irreversibly deletes data. Ensure you have appropriate backups and permissions before executing such commands.

Remember to replace current_schema() with the desired schema if you wish to truncate tables from a specific schema other than the current one.

By following these steps, you can efficiently truncate all tables in PostgreSQL, simplifying tasks like database cleanup or preparing for testing scenarios.

свернуть