TRUNCATE TABLE in PostgreSQL

TRUNCATE TABLE removes all rows from a table by deallocating entire data pages, rather than deleting rows one by one. Unlike DELETE, it does not scan rows, does not fire ON DELETE triggers, and leaves no dead tuples for vacuum to clean up. The table structure, indexes, and constraints remain intact after truncation.

Syntax

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

Truncate multiple tables in one statement:

TRUNCATE TABLE orders, order_lines;

Reset associated identity or SERIAL sequences:

TRUNCATE TABLE products RESTART IDENTITY;

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

Practical Example

Set up a products table with some data:

CREATE TABLE products (
  product_id  SERIAL PRIMARY KEY,
  sku         VARCHAR(50) NOT NULL UNIQUE,
  name        VARCHAR(200) NOT NULL,
  unit_price  NUMERIC(10,2) NOT NULL
);

INSERT INTO products (sku, name, unit_price)
VALUES
  ('WID-001', 'Basic Widget',   9.99),
  ('WID-002', 'Premium Widget', 24.99),
  ('WID-003', 'Deluxe Widget',  49.99);

Clear all rows and reset the sequence:

TRUNCATE TABLE products RESTART IDENTITY;

-- Verify the table is empty
SELECT COUNT(*) FROM products;
-- 0

-- Next insert gets product_id = 1 again
INSERT INTO products (sku, name, unit_price)
VALUES ('WID-NEW', 'New Widget', 14.99)
RETURNING product_id;
-- product_id = 1

Handle a parent table with foreign key references:

CREATE TABLE orders (
  order_id    SERIAL PRIMARY KEY,
  placed_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_lines (
  line_id    SERIAL PRIMARY KEY,
  order_id   INT NOT NULL,
  product_id INT NOT NULL,
  quantity   INT NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE
);

-- Attempting to truncate the parent alone raises an error:
TRUNCATE TABLE orders;
-- ERROR: cannot truncate a table referenced in a foreign key constraint
-- HINT: Truncate table "order_lines" at the same time, or use TRUNCATE ... CASCADE.

-- Use CASCADE to truncate both:
TRUNCATE TABLE orders CASCADE;
-- NOTICE: truncate cascades to table "order_lines"

Wrap in a transaction for safe testing:

BEGIN;
TRUNCATE TABLE order_lines, orders RESTART IDENTITY;
-- Verify before committing
SELECT COUNT(*) FROM orders;
COMMIT;  -- or ROLLBACK to undo

TRUNCATE vs DELETE

Testing with Vela

Before truncating production tables — especially with CASCADE — Vela database branching lets you run the TRUNCATE on a copy of the live database to see exactly which tables are affected and verify the RESTART IDENTITY behavior. Test your reload pipeline immediately after to confirm new inserts work correctly, then apply to production with confidence.

Production Tips

• Use TRUNCATE instead of DELETE with no WHERE clause when clearing large tables — it is orders of magnitude faster and produces far less WAL traffic.
• Add RESTART IDENTITY when you want new inserts after the truncate to start from ID 1 (or the sequence start value).
• Use CASCADE carefully — verify the full cascade list with \d+ table_name before running in production.
• Wrap destructive truncates in a BEGIN / COMMIT block so you can ROLLBACK if something unexpected is affected.
• TRUNCATE does not fire row-level ON DELETE triggers — if trigger logic must run, use DELETE instead.
• Because TRUNCATE generates minimal WAL, it can be much faster than DELETE even on tables with moderate row counts.