How to Truncate PostgreSQL Tables
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:
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor prese
Active: active (exited) since Thu 2019-08-01 14:51:20 PST; 36min ago
Process: 1230 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 1230 (code=exited, status=0/SUCCESS)
Aug 01 14:51:20 user-UX330UAK systemd: Starting PostgreSQL RDBMS...
Aug 01 14:51:20 user-UX330UAK systemd: Started PostgreSQL RDBMS.
lines 1-8/8 (END)
Execute the following actions in sequence to start, stop and restart the PostgreSQL server with a Windows machine:
- Open Control Panel
- Open Administrative Tools
- Open Services
- Find the PostgreSQL Server service
- Start, Stop or Restart the service
Understanding the PostgreSQL Truncate Table Syntax
Following is the PostgreSQL
[ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]
A step-by-step discussion of the above syntax follows:
nameparameter used in the PostgreSQL
TRUNCATEfunction is the same as the name of the table being truncated. Specifying the
ONLYoption before the table name indicates that only that individual table will be truncated. If the
ONLYstatement 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.
RESTART IDENTITYcommand will cause the sequences owned by the truncated table column to be restarted automatically.
CONTINUE IDENTITYcommand will maintain the sequence.
CASCADEcommand will make every table that posses a foreign-key reference to any other table automatically be truncated.
RESTRICTcommand will prevent the
TRUNCATEfunction 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
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:
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started