TRUNCATE table example in PostgreSQL.

In a previous blog post, I selected records from the staging_assets table to insert into their respective table, depending on the type of asset. Let’s empty out this table, yet keep the structure in place for future uploads, using the TRUNCATE command.

Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.

I will be using Xubuntu Linux 16.04.2 LTS (Xenial Xerus) and PostgreSQL 9.6.4 for these exercises.

TRUNCATE removes all rows from either a table or set of tables. It is similar to an unfiltered DELETE (A DELETE without a WHERE clause for filtering) command with the exception that it is faster since no table scan is performed.

To start, I will retrieve a description of the staging_assets table as follows:

We can see that there are no foreign-keys in this table. When using the TRUNCATE command, foreign-keys play a role in what records are removed from linked tables through those keys.
The below SELECT statement returns a total count of 45 records present in the table:

Basic TRUNCATE command syntax can be:

TRUNCATE table_name;

I will briefly touch on the available optional parameters:

• RESTART IDENTITY: Will automatically restart any sequences owned by truncated table columns. (The staging_assets table contains no sequences)
• CONTINUE IDENTITY: Does not change the value of any sequences. (This is the default.)
• CASCADE: Automatically TRUNCATE any tables that have a foreign-key relationship with the named table. We checked for this above but will visit an example shortly.
• RESTRICT: Will not TRUNCATE if any of the tables have foreign-key references from tables that are not listed in the command. (This is also the default setting.)

For this post, I will use an elementary TRUNCATE on the staging_assets table:

Note: By specifying ONLY before the table name, only that table is truncated.

And re-checking the count of records:

Returning a count of 0, the TRUNCATE command is successful.
Let’s make sure the table structure is still in place.

All columns, data types, and any modifiers or constraints are still present allowing us to continue to insert rows into this table when necessary.

A side note on CASCADE and foreign-keys.

Let us briefly visit a scenario where foreign-keys need to be considered when using the TRUNCATE command.
The fab_tracking database contains a weld_log table with the following structure:

Notice this table has a foreign-key relationship with the pup_assets table. Suppose we wanted to TRUNCATE that table? We can just issue the below command right?

TRUNCATE TABLE ONLY pup_assets;

Let’s see.

This is a great example of when the CASCADE option can be used (We even get a hint for this as shown on the last line of the returned error message.) or both the weld_log and pup_assets tables can be listed together in a single TRUNCATE command. However, exercise caution when cascading other tables in TRUNCATE commands, since those records are removed as well.

The staging_assets table is a perfect candidate for TRUNCATE use. Since this table temporarily houses records until they are cleaned, formatted, grouped, etc.. Once the records are moved on, this table can still be used for future staging, therefore quickly be cleaned out. Visit the TRUNCATE command documentation for an in-depth overview.

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

Edited:07/08/2018