Postgres Truncate Table

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

One of the benefits of using PostgreSQL is that you can store and manage large amounts of information within a database. When huge bits of table data become outdated or are no longer useful for any reason, you’ll want to get rid of it as quickly as possible. Luckily, PostgreSQL has a handy statement that can do all of the work for you. It’s called Postgres truncate table, also referred to as TRUNCATE TABLE. With that statement, you’ll be able to remove large datasets within the target table.

Prerequisite

What is Postgres TRUNCATE TABLE

The Postgres truncate table statement bypasses the scanning process that is typically required before a deletion operation takes place. Skipping scanning enables you to have every row removed from the table you target very fast. In fact, it’s speedier than using Postgres DELETE statements.

In addition, the operation VACUUM isn’t needed. Normally, after an update, you would use VACUUM to delete obsolete database records and thereby, save space. If you don’t perform VACUUM regularly on your database, it will eventually become too large. Truncate table statement eliminates the VACUUM step so you reclaim the space you need for your large data table in the least amount of time.

Remove all data in single table

Use Postgres truncate table to do away with the data in one table.

  • Start by using using the statement below:
1
TRUNCATE TABLE <table_name>;

The code above shows you how to begin the TRUNCATE TABLE statement. Next, inside the parameters, you would input the name of the table.

  • To get rid of every employee table row, use this statement:
1
TRUNCATE TABLE employee;

>NOTE: Use the RESTART IDENTITY clause to reset tables after you use the Postgres truncate table TRUNCATE TABLE statement. You can enter the clause directly following entering the table name within the statement.

1
TRUNCATE TABLE table_name RESTART IDENTITY;

For example, take a look at the statement below. It identifies employee as a sample table. Use RESTART IDENTITY to automatically reset the sequence associated with the employee_number column like this:

1
TRUNCATE TABLE employee RESTART IDENTITY;

>NOTE: Using the Postgres truncate table alone won’t reset any sequence it’s associated with. As indicated in the complete TRUNCATE TABLE statement above, you’ll need to use RESTART IDENTITY to perform the reset. As an alternative, use the CONTINUE IDENTITY option to explicitly keep the sequence.

Removing entire data from multiple tables

  • Delete data from several tables simultaneously with Postgres truncate table using this statement:
1
TRUNCATE TABLE table1, table2 ...

>NOTE: Use a comma (,) between database table listings.

1
TRUNCATE TABLE employee, finance;

The statement above illustrates how you can bypass scanning and still remove every single table row in the following tables: finance and employee.

Remove all data with foreign keys

It’s common for databases to include foreign keys associated with table references. However, Postgres truncate table doesn’t automatically remove that data. You’ll want to delete them when you use the TRUNCATE TABLE statement that applies to tables with foreign keys.

The statement below shows the CASCADE option selected to remove the foreign key reference from the finance table.

1
TRUNCATE TABLE finance CASCADE;

Without inputting the CASCADE option, an error message will result that appears like this:

1
2
3
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "employee" references "finance".
HINT: Truncate table "employee" at the same time, or use TRUNCATE ... CASCADE.

>NOTE: The CASCADE option is the only way to remove the foreign key data in the Postgres truncate table. If you try to use CASCADE UPDATE or DELETE, you’ll receive an error message.

1
TRUNCATE TABLE finance CASCADE;

Below shows a successful result after using the option CASCADE to remove table references to foreign keys:

1
NOTICE: truncate cascades to table "employee"

Conclusion

Postgres truncate table aka TRUNCATE TABLE, is the statement that can streamline managing the removal of table rows. Because it doesn’t scan prior to deletion, it’s advantageous when you have large table data that you want to remove speedily. What’s more, the removal of foreign key table references is important. It just lingers there taking up space after an associated table is gone. Since eliminating the foreign keys doesn’t happen by default, it’s also great to know that you can simply do away with them with the CASCADE option. Use both strategies today.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.