intro
In this tutorial, you will learn how to use PostgreSQL TRUNCATE TABLE statement to quickly delete all data from tables.
When managing PostgreSQL databases, efficiently removing large amounts of data is a common challenge that database administrators and developers face daily. While the DELETE statement might seem like the obvious choice, PostgreSQL offers a more powerful and efficient alternative: the TRUNCATE TABLE command. This comprehensive guide talks about everything you need to know about PostgreSQL TRUNCATE TABLE, from basic syntax to advanced use cases with real-world examples — let’s get right into it!
What is the PostgreSQL TRUNCATE TABLE Statement?
In PostgreSQL, TRUNCATE is a statement to quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table, but since it does not actually scan the tables, it is faster. Furthermore, it reclaims disk space immediately, rather than requiring a subsequent VACUUM operation.
The PostgreSQL TRUNCATE TABLE statement is an SQL DDL (Data Definition Language) command that provides a fast and efficient way to delete all rows from one or more tables while preserving the table structure, indexes, and constraints—and even sequences.
Key Characteristics of the TRUNCATE TABLE Postgres Statement
TRUNCATE TABLE PostgreSQL Syntax
The syntax for the PostgreSQL TRUNCATE TABLE statement is as follows:
1
TRUNCATE TABLE table_name;
In the syntax, you simply declare the name of the table in question after the TRUNCATE TABLE statement.
Postgres TRUNCATE TABLE Examples
Time to explore some examples of using the TRUNCATE TABLE statement in PostgreSQL.
Note: All sample queries in this article are going to be demonstrated in DbVisualizer—the database client with the highest user satisfaction in the market. Any other database client will do.
Example #1: Truncate a Single Table
Consider a PostgreSQL database with these tables in it:
Observe the existing data in the target table (departments) first:
Assuming that we want to get rid of all the data in the departments table while maintaining the table structure, indexes and constraints, we would simply do:
1
TRUNCATE TABLE departments;
Now, this is how our target table looks like in a PostgreSQL database client like DbVisualizer—confirming a successful execution of our TRUNCATE TABLE statement:
Great! This is very simple.
Example #2: Truncate Multiple Tables
Assume for example, that you find yourself in a situation where you have to delete table from multiple tables. You implement the basic syntax of the PostgreSQL TRUNCATE TABLE statement followed by the multiple table_name declaration of the tables in question, like this:
1
TRUNCATE TABLE table1_name, table2_name;
For example, this would be the query if we were to get rid of these tables: customer_addresses, and customers.
1
TRUNCATE TABLE customer_addresses, customers;
Got it! This example shows how to use TRUNCATE TABLE with multiple tables in a single statement, demonstrated with the customer_addresses and customers tables for efficient bulk data removal.
Find out in our article on how to truncate many tables at once in SQL.
Example #3: Truncate Table With Foreign Key
In this example, we’re going to consider using TRUNCATE TABLE PostgreSQL statement to delete data from a table referenced by a foreign key. Foreign key relationships require special consideration when using TRUNCATE TABLE.
Consider running this query in the SQL Commander of DbVisualizer:
1
CREATE TABLE orders(
2
order_id SERIAL PRIMARY KEY,
3
customer_name VARCHAR(255) NOT NULL,
4
ordered_date DATE NOT NULL,
5
status VARCHAR(20) NOT NULL
6
);
7
8
CREATE TABLE order_items (
9
order_id INT NOT NULL,
10
item_id INT NOT NULL,
11
product_name VARCHAR(255) NOT NULL,
12
quantity INT NOT NULL,
13
FOREIGN KEY (order_id)
14
REFERENCES orders(order_id) ON DELETE CASCADE ON UPDATE CASCADE,
15
PRIMARY KEY (order_id, item_id)
16
);
17
18
INSERT INTO orders (customer_name, ordered_date, status)
19
VALUES
20
('John Wesley', '2020-01-01', 'Delivered'),
21
('Leslie Gyamfi', '2021-01-26', 'Delivered'),
22
('Ed Wuncler II', '2025-05-31', 'Shipped');
23
24
INSERT INTO order_items (order_id, item_id, product_name, quantity)
25
VALUES
26
(1, 1, 'Xbox', 3),
27
(1, 2, 'PS5', 2),
28
(2, 1, 'Mac Mini', 1),
29
(3, 1, 'Apple Series 8', 1),
30
(3, 2, 'Iphone 13 Pro Max', 1);
Now, try to truncate data from our orders table and PostgreSQL will throw an error as shown here in DbVisualizer:
This is because the order_items table has a foreign key reference to the orders table. There are several ways to handle this, the recommended way is to truncate tables in the dependency order like this:
1
-- Clear child table first, then parent table
2
TRUNCATE TABLE order_items;
3
TRUNCATE TABLE orders;
But also, you can use the most direct CASCADE option:
1
TRUNCATE TABLE orders CASCADE;
And that’s a wrap!
Conclusion
The PostgreSQL TRUNCATE TABLE statement is a useful command for efficiently managing data removal operations. Its superior performance characteristics make it ideal for scenarios involving complete table clearing, particularly in data warehousing, and test environment management.
Fire up your TRUNCATE operations in DbVisualizer and you'll benefit from its comprehensive PostgreSQL support and visual feedback. DbVisualizer's SQL Commander provides immediate error reporting for foreign key constraint violations, as shown in the interface screenshot in this guide.
Start experimenting with the examples provided. Your data is waiting to be transformed into actionable insights. Happy querying!
FAQ
What is TRUNCATE in PostgreSQL?
TRUNCATE is a PostgreSQL command that quickly removes all rows from one or more tables while preserving the table structure, indexes, and constraints. Unlike DELETE, which processes rows individually, TRUNCATE operates at the table level by essentially creating new, empty data files making it extremely fast for clearing entire tables.
What's the difference between TRUNCATE and DELETE in PostgreSQL?
TRUNCATE removes all rows from a table without scanning individual rows, making it much faster for large datasets. It also immediately reclaims disk space and can reset identity sequences. DELETE processes rows individually, is slower for complete table clearing, but allows conditional removal with WHERE clauses and fires ON DELETE triggers.
Can I undo a TRUNCATE operation?
Yes, TRUNCATE is fully transactional in PostgreSQL. If executed within a transaction block (BEGIN/COMMIT), you can use ROLLBACK to undo the operation. However, once the transaction is committed, the data cannot be recovered without restoring from a backup.
Why do I get a foreign key constraint error when using TRUNCATE?
This error occurs when other tables have foreign key references to the table you're trying to truncate. You can resolve this by either truncating dependent tables first, using the CASCADE option (which truncates all related tables), or truncating multiple tables simultaneously in one command.
