Truncate Table | PostgreSQL Tutorial

TRUNCATE TABLE removes all rows from a table without scanning them individually. Unlike DELETE, it deallocates entire data pages, making it orders of magnitude faster on large tables. The table structure, indexes, and constraints remain intact.

Syntax and key options

TRUNCATE TABLE table_name [RESTART IDENTITY | CONTINUE IDENTITY] [CASCADE | RESTRICT];

To truncate multiple tables at once, separate names with commas:

TRUNCATE TABLE customers, vendors;

Reset auto-increment sequences along with the data using RESTART IDENTITY:

TRUNCATE TABLE products RESTART IDENTITY;

The default is CONTINUE IDENTITY, which leaves sequence values unchanged.

Practical examples

Basic truncate — remove all rows from the products table:

CREATE TABLE products (
  id    SERIAL PRIMARY KEY,
  name  VARCHAR(255) NOT NULL,
  price DECIMAL(10, 2) NOT NULL DEFAULT 0
);

INSERT INTO products (name, price)
VALUES ('A', 19.99), ('B', 29.99), ('C', 39.99), ('D', 49.99)
RETURNING *;

TRUNCATE TABLE products;
-- TRUNCATE TABLE

When a table is referenced by a foreign key, a plain TRUNCATE fails:

CREATE TABLE orders (
  order_id      SERIAL PRIMARY KEY,
  customer_name VARCHAR(255) NOT NULL,
  ordered_date  DATE NOT NULL,
  status        VARCHAR(20) NOT NULL
);

CREATE TABLE order_items (
  order_id     INT NOT NULL,
  item_id      INT NOT NULL,
  product_name VARCHAR(255) NOT NULL,
  quantity     INT NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
  PRIMARY KEY (order_id, item_id)
);

-- Attempting to truncate the parent table alone raises an error:
TRUNCATE TABLE orders;
-- DETAIL:  Table "order_items" references "orders".
-- HINT:    Truncate table "order_items" at the same time, or use TRUNCATE ... CASCADE.

Use CASCADE to truncate both tables simultaneously:

TRUNCATE TABLE orders CASCADE;
-- NOTICE:  truncate cascades to table "order_items"

Why TRUNCATE is faster than DELETE

Minimal logging — TRUNCATE deallocates entire data pages rather than generating an individual log entry per row.
Fewer resources — it produces far less undo/redo information than a full-table DELETE.
Lower-level locking — TRUNCATE acquires an ACCESS EXCLUSIVE lock on the table but completes much faster, reducing overall contention.
Does not fire ON DELETE triggers — if trigger execution is required, use DELETE instead. TRUNCATE fires BEFORE TRUNCATE / AFTER TRUNCATE triggers only.
Transaction-safe — TRUNCATE is fully transactional; wrap it in a BEGIN/COMMIT and roll back if needed.

Frequently Asked Questions

What is the difference between TRUNCATE and DELETE in PostgreSQL?

TRUNCATE removes all rows by deallocating data pages — it is very fast, does not fire ON DELETE triggers, and does not support a WHERE clause. DELETE removes rows one at a time, fires ON DELETE triggers, supports WHERE filtering, and generates more transaction log. For clearing large tables quickly, prefer TRUNCATE.

Does TRUNCATE TABLE reset auto-increment sequences?

Not by default. Use TRUNCATE TABLE table_name RESTART IDENTITY; to also reset the sequence associated with identity or SERIAL columns back to its starting value.

Can TRUNCATE TABLE remove data from tables with foreign keys?

By default it is blocked if other tables have foreign key references pointing to the table. Add CASCADE to truncate the parent table and all referencing tables at the same time: TRUNCATE TABLE orders CASCADE;

Is TRUNCATE TABLE safe to use inside a transaction?

Yes. TRUNCATE is fully transactional in PostgreSQL. You can wrap it in BEGIN/COMMIT and roll back the transaction to undo the operation.

Does TRUNCATE TABLE fire triggers?

TRUNCATE does not fire row-level ON DELETE triggers. It only fires statement-level BEFORE TRUNCATE and AFTER TRUNCATE triggers if those have been defined on the table.