How to Truncate PostgreSQL Tables

Introduction

This tutorial will explain how to truncate PostgreSQL tables. The PostgreSQL TRUNCATE function statement is used to delete all the data within a table or multiple tables without having to first scan the tables. Because of this feature, the PostgreSQL Truncate function is much more efficient than the PostgreSQL DELETE statement. Additionally, the truncate function recovers disk space instantaneously, eliminating the need for having to process a VACUUM operation. This makes the truncate function much more useful for editing larger tables.

Prerequisites to Truncate PostgreSQL Tables

• The PostgreSQL server must be properly installed, configured and running. Download PostgreSQL for Linux and Windows systems here

Execute the following command to start the PostgreSQL server using a LINUX machine:

• Verify if the PostgreSQL service is running with the following command:

The results should resemble following:

Execute the following actions in sequence to start, stop and restart the PostgreSQL server with a Windows machine:

1. Open Control Panel
2. Open Administrative Tools
3. Open Services
4. Find the PostgreSQL Server service
5. Start, Stop or Restart the service

Understanding the PostgreSQL Truncate Table Syntax

Following is the PostgreSQL TRUNCATE syntax:

A step-by-step discussion of the above syntax follows:

• The name parameter used in the PostgreSQL TRUNCATE function is the same as the name of the table being truncated. Specifying the ONLY option before the table name indicates that only that individual table will be truncated. If the ONLY statement is not used, then all of the subordinate tables will be truncated. The asterisk (*) character or wild-card can also be used after the table name to indicate that the table’s subordinates are also to be included in the function.

• The RESTART IDENTITY command will cause the sequences owned by the truncated table column to be restarted automatically.

• The CONTINUE IDENTITY command will maintain the sequence.

• The CASCADE command will make every table that posses a foreign-key reference to any other table automatically be truncated.

• The RESTRICT command will prevent the TRUNCATE function from being executed if any tables not included in the command have a reference from the table’s foreign key.

How to Truncate All Data from One Table Using PostgreSQL TRUNCATE Command

With a basic understanding of the PostgreSQL TRUNCATE syntax, this section will use a simple example of how to use the syntax.

The following statement shows how to use the most basic and simplest form of the TRUNCATE TABLE statement:

When wanting to truncate all the rows or data within the table employee, use the following command:

Following is another example of a PostgreSQL TRUNCATE command that will remove the data from the table and also allow for resetting the table-sequence generator with the RESTART IDENTITY clause:

The following example replaces the “your_table_name” phrase, shown above, with the table name employee:

NOTE: The PostgreSQL TRUNCATE TABLE expression won’t reset the sequence associated with it. As mentioned in the previous section where the parts of the syntax were broken down, the CONTINUE IDENTITY parameter can be used within the statement to maintain the sequence.

How to Truncate All Data from Multiple Tables using PostgreSQL TRUNCATE Command

The previous section covered how to use the PostgreSQL TRUNCATE command for single tables. This section will explain how to use the TRUNCATE command on multiple tables.

All the data can be deleted from multiple tables simultaneously by specifically naming each table, separated by a comma, as shown here:

To use the above syntax, replace the sample_table_names with the names of the tables that are to be truncate, as follows:

How to Truncate All the Data from a Table with the Foreign-Key Reference using the PostgreSQL TRUNCATE Command.

In practice, tables typically have foreign-key references to other tables that are not included in the PostgreSQL TRUNCATE TABLE statement. By default, data within a table with references to foreign keys will not be removed by the PostgreSQL TRUNCATE TABLE clause.

In order to remove all of the data in the main table, and other tables that have a foreign-key reference to the main table, the truncate option CASCADE command is executed as follows:

As an example, to delete all of the data from the table employee and be able to cascade the command to all the tables that have references to employee table with a foreign key, execute the following command:

Conclusion

This tutorial explained how to truncate PostgreSQL tables using the PostgreSQL Truncate command. The article covered how to run the PostgreSQL server with both Windows and Linux machines and provided a step-by-step breakdown of the PostgreSQL TRUNCATE syntax. The tutorial also explained how to truncate all of the data from from individual and multiple tables using PostgreSQL Truncate command. Remember that the PostgreSQL TRUNCATE TABLE expression will not reset the sequence associated with it and the CONTINUE IDENTITY parameter is used within the statement to maintain the sequence.