TRUNCATE is a DML command for quickly deleting the contents of a table.
TRUNCATE was added in PostgreSQL 7.0.
TRUNCATE and locking
In contrast to
TRUNCATE requires an
AccessExclusiveLock on a table before its contents can be removed. While
TRUNCATE itself is an extremely fast operation, on an active database, there may be an unpredicatble delay before the lock can be acquired and the
TRUNCATE command will appear to "hang".
- PostgreSQL 8.4
- PostgreSQL 8.2
RESTRICToptions added (commit 984a6ced)
- PostgreSQL 8.1
- multiple table names can be provided (commit f07b9689)
- PostgreSQL 7.0
- added (commit e7cad7b0)
postgres=# TRUNCATE TABLE foo; TRUNCATE TABLE
Attempt to truncate a table with foreign key references:
postgres=# CREATE TABLE foo (id INT PRIMARY KEY); CREATE TABLE postgres=# CREATE TABLE bar (id INT, foo_id INT NOT NULL REFERENCES foo (id)); CREATE TABLE postgres=# TRUNCATE TABLE foo; ERROR: cannot truncate a table referenced in a foreign key constraint DETAIL: Table "bar" references "foo". HINT: Truncate table "bar" at the same time, or use TRUNCATE ... CASCADE.
TRUNCATE TABLE foo is the same as
TRUNCATE TABLE foo RESTRICT. To recursively truncate data from referencing tables, use
postgres=# TRUNCATE TABLE foo CASCADE; NOTICE: truncate cascades to table "bar" TRUNCATE TABLE
It's also possible to explictly list the tables to be truncated:
postgres=# TRUNCATE TABLE foo, bar; TRUNCATE TABLE