PostgreSQL TRUNCATE All Tables Generator

Paste a schema here and this generator writes the statement that empties it. To truncate all tables, Postgres wants every one of them named in a single TRUNCATE or reached by CASCADE, and both come out below, along with the DELETE order for databases where cascading is refused.

Schema DDL

--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';

CREATE SCHEMA shop;
CREATE SCHEMA billing;

CREATE TABLE shop.customers (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    email text NOT NULL,
    default_address_id bigint,
    created_at timestamptz DEFAULT now() NOT NULL
);

CREATE TABLE shop.addresses (
    id bigserial PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES shop.customers(id),
    line1 text NOT NULL,
    postcode text
);

CREATE TABLE shop.orders (
    id bigserial PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES shop.customers(id) ON DELETE CASCADE,
    ship_to_id bigint REFERENCES shop.addresses(id),
    placed_at timestamptz DEFAULT now() NOT NULL
);

CREATE TABLE shop.order_lines (
    id bigserial PRIMARY KEY,
    order_id bigint NOT NULL REFERENCES shop.orders(id) ON DELETE CASCADE,
    sku text NOT NULL,
    quantity integer NOT NULL
);

CREATE TABLE billing.invoices (
    id bigserial PRIMARY KEY,
    order_id bigint NOT NULL REFERENCES shop.orders(id),
    issued_on date NOT NULL,
    total_cents integer NOT NULL
);

CREATE TABLE billing.payments (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    invoice_id bigint NOT NULL REFERENCES billing.invoices(id),
    paid_at timestamptz,
    amount_cents integer NOT NULL
);

ALTER TABLE ONLY shop.customers
    ADD CONSTRAINT customers_default_address_id_fkey
    FOREIGN KEY (default_address_id) REFERENCES shop.addresses(id) DEFERRABLE INITIALLY DEFERRED;

--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';

CREATE SCHEMA shop;
CREATE SCHEMA billing;

CREATE TABLE shop.customers (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    email text NOT NULL,
    default_address_id bigint,
    created_at timestamptz DEFAULT now() NOT NULL
);

CREATE TABLE shop.addresses (
    id bigserial PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES shop.customers(id),
    line1 text NOT NULL,
    postcode text
);

CREATE TABLE shop.orders (
    id bigserial PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES shop.customers(id) ON DELETE CASCADE,
    ship_to_id bigint REFERENCES shop.addresses(id),
    placed_at timestamptz DEFAULT now() NOT NULL
);

CREATE TABLE shop.order_lines (
    id bigserial PRIMARY KEY,
    order_id bigint NOT NULL REFERENCES shop.orders(id) ON DELETE CASCADE,
    sku text NOT NULL,
    quantity integer NOT NULL
);

CREATE TABLE billing.invoices (
    id bigserial PRIMARY KEY,
    order_id bigint NOT NULL REFERENCES shop.orders(id),
    issued_on date NOT NULL,
    total_cents integer NOT NULL
);

CREATE TABLE billing.payments (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    invoice_id bigint NOT NULL REFERENCES billing.invoices(id),
    paid_at timestamptz,
    amount_cents integer NOT NULL
);

ALTER TABLE ONLY shop.customers
    ADD CONSTRAINT customers_default_address_id_fkey
    FOREIGN KEY (default_address_id) REFERENCES shop.addresses(id) DEFERRABLE INITIALLY DEFERRED;

TRUNCATE statement

TRUNCATE TABLE shop.customers, shop.addresses,  shop.orders, shop.order_lines, billing.invoices,  billing.payments RESTART IDENTITY CASCADE;

Tables to emptyAll

shop.customers2 foreign keys point here
shop.addresses2 foreign keys point here
shop.orders2 foreign keys point here
shop.order_lines0 foreign keys point here
billing.invoicesone foreign key points here
billing.payments0 foreign keys point here

The number on a row is how many foreign keys in the schema point at that table.

The statement follows the schema as you type, in this tab and nowhere else. Ctrl+Enter, Cmd+Enter on a Mac, rereads it at once.

What it does and how to use it

Paste what pg_dump --schema-only gave you, or a few CREATE TABLE statements written by hand. Tables and the foreign keys between them are what the parser keeps, and the SET and GRANT lines a dump wraps around them are stepped over. Under the panel sits every table it found, each with the number of keys pointing at it.

Untick one and the line beneath the list names the tables CASCADE empties anyway, because a truncation reaches whatever holds a key into a table being truncated, and then whatever holds a key into those. Where cascading is not on offer, the DELETE order tab writes one DELETE FROM per table with the children first, inside a transaction. The third tab is for a reader who has the database in front of them and no schema file, and hands over a DO block that reads pg_tables.

To truncate all tables, Postgres asks for CASCADE or the whole list

Run TRUNCATE against one table of a schema that has foreign keys and PostgreSQL 18 answers cannot truncate a table referenced in a foreign key constraint, then hints at the table you left out. Naming every referencing table in the same statement clears it, which is what this page does by default. CASCADE clears it too, and hands the server the job of working out what else goes.

ON DELETE CASCADE on the child is the clause people reach for here, and it does nothing for a truncation. That clause governs DELETE, and the parent on its own is refused exactly as before.

TRUNCATE against DELETE, and what RESTART IDENTITY really does

RESTART IDENTITY sends the sequences owned by the truncated columns back to their start value, so the next row inserted is id 1 again. Write neither word and you get CONTINUE IDENTITY, which leaves them where they stand. Deleting rows changes no sequence at all.

A busy database notices the lock before it notices anything else. TRUNCATE takes ACCESS EXCLUSIVE on every table it names and holds it until the transaction ends, so readers wait; DELETE takes ROW EXCLUSIVE and they carry on. The trade the other way is that a truncation frees the space at once, where deleted rows sit until VACUUM reaches them, and row triggers fire for the delete and never for the truncation.

FAQ

Frequently asked questions

Not when every table in the schema is named in the one statement, which is what this generator writes by default. CASCADE starts to matter once you empty part of a schema, because PostgreSQL refuses a partial list and CASCADE then reaches tables you never named. The line under the table list says which ones, before you run anything.

A weaker lock keeps readers going while it runs, and the row triggers a truncation skips do fire on a delete. It is also the way in where the role is not allowed to truncate at all. The cost is speed and disk, since deleted rows sit there until VACUUM reaches them.

Row triggers do not fire on TRUNCATE, which has statement-level triggers of its own. Setting session_replication_role to replica switches off the constraint triggers behind foreign keys, so a DELETE runs in any order, and it needs the privilege to set that parameter. On PostgreSQL 18 it left child rows pointing at parents that were already gone, so the whole closure has to go in one transaction.

Rolling the transaction back puts the sequence back with the rows. On PostgreSQL 18 the next insert after such a rollback took the id it would have taken had none of it happened. The call that survives a rollback is setval, which PostgreSQL documents as non-transactional, and RESTART IDENTITY does not go through it.

It does not. The DDL is parsed by JavaScript in this tab and the statement is assembled there as well, so there is nothing to upload and no server to upload it to. The one thing recorded is that the tool ran, under its own name, with none of your text attached.

It is free and it asks for no account. Nothing here is rate limited either, because the work happens on your own machine and a longer dump costs us nothing.

Something has to go back in, since a schema emptied before a test run still needs rows. Reading a live schema and filling it from that is covered in the guide on seeding a database. Sequences behaving oddly once rows come back after a DELETE is the subject of the article linked above.

More tools

PostgreSQL TRUNCATE All Tables Generator

What it does and how to use it

To truncate all tables, Postgres asks for CASCADE or the whole list

TRUNCATE against DELETE, and what RESTART IDENTITY really does

Frequently asked questions

Other free PostgreSQL tools