TRUNCATE

A DML command for quickly deleting the contents of a table

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 DELETE, 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".

Change history

Examples

Basic TRUNCATE execution:

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.

Note that TRUNCATE TABLE foo is the same as TRUNCATE TABLE foo RESTRICT. To recursively truncate data from referencing tables, use CASCADE.

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

Categories

DML, SQL command

See also

DELETE, DROP TABLE