PostgreSQL TRUNCATE All Tables Generator

Paste a schema here and this generator writes the statement that empties it. To have Postgres truncate all tables in one go, name every one of them in a single TRUNCATE customers, orders, order_items RESTART IDENTITY CASCADE;, or name the parents and let CASCADE follow the foreign keys to the rest. Where cascading is refused, the DELETE order comes out below as well.

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.

Truncate every table: the statement

Every table in one statement is the form that needs nothing else, because a foreign key only blocks a truncation when the table on its other end is missing from the list.

TRUNCATE customers, orders, order_items RESTART IDENTITY CASCADE;

Name only the parents and CASCADE does the walking. It empties every table holding a key into one you named, then whatever holds a key into those, across schemas as well, so TRUNCATE customers CASCADE on PostgreSQL 18 cleared orders, order_items and an audit.log table in another schema that pointed at customers. ON DELETE CASCADE on the child changes none of this, since that clause governs DELETE, and a truncation of the parent alone is refused exactly as before.

TRUNCATE customers CASCADE;

With the database in front of you and no DDL to paste, a DO block can read the table list out of pg_tables and build the statement itself. Change 'public' to the schema you mean and it truncates all tables in that schema, whatever the last migration added. Run against a three-table schema on PostgreSQL 18 it assembled TRUNCATE TABLE public.order_items, public.customers, public.orders RESTART IDENTITY CASCADE, left every count at zero and handed the next insert id 1.

DO $$
DECLARE
    statement text;
BEGIN
    SELECT 'TRUNCATE TABLE '
           || string_agg(format('%I.%I', schemaname, tablename), ', ')
           || ' RESTART IDENTITY CASCADE'
      INTO statement
      FROM pg_tables
     WHERE schemaname = 'public';

    IF statement IS NOT NULL THEN
        EXECUTE statement;
    END IF;
END
$$;

cannot truncate a table referenced in a foreign key constraint

That line is what PostgreSQL 18 answers when the list is incomplete, followed by a DETAIL line, Table "orders" references "customers", and a HINT, Truncate table "orders" at the same time, or use TRUNCATE ... CASCADE. The hint is the whole fix. Add the table it names to the statement, together with any table that in turn references that one, or put CASCADE at the end and let the server find them. The refusal exists because TRUNCATE never reads the rows it removes, and confirming that no child row would be left pointing at nothing would take exactly the table scan the command is there to avoid.

TRUNCATE, DELETE or DROP

TRUNCATE keeps the table and its indexes and discards the contents in one step, where DELETE walks the rows, fires their triggers and leaves the space for VACUUM to reclaim. The third option removes the table itself rather than its rows. DROP TABLE customers is refused while orders still carries a key into it, and on PostgreSQL 18 DROP TABLE customers CASCADE took the table and that constraint away together but left the two rows in orders where they were, pointing at nothing. Reach for DROP when migrations rebuild the schema anyway, and for one of the other two when the schema has to survive the reset.

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 the DO block above.

RESTART IDENTITY, locks and the DELETE order

A Postgres TRUNCATE with RESTART IDENTITY sends the sequences owned by the truncated columns back to their start value, so the next row inserted is id 1 again, which a PostgreSQL 18 run confirmed for an identity column and a serial alike. Write neither word and you get CONTINUE IDENTITY, which leaves them where they stand, and a plain DELETE changes no sequence at all. When a sequence has drifted away from the rows for some other reason, the article on the insert that fails right after a successful load covers setval and the resync.

On a busy database the lock is what gets noticed first. 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.

To truncate tables in order, Postgres asks for nothing, since one statement empties them all at once. Order matters on the DELETE route alone, where the children go before the parents and the whole list runs inside one transaction, which is what the second tab writes.

FAQ

Frequently asked questions

Run the DO block above with the schema name in its WHERE clause, or paste the schema's DDL into the generator and copy the statement it writes. Both produce one TRUNCATE naming every table, so nothing is left for a foreign key to object to. Tables in other schemas are only touched when CASCADE reaches them through a key.

Because TRUNCATE removes a table's contents without reading them, it has no way to confirm that no row in a referencing table would be left pointing at a parent that is gone. The manual says checking that would take the table scan the command exists to avoid, so PostgreSQL refuses unless the referencing table is in the same statement or CASCADE is given. A self-referencing table is fine on its own, since the referencing table is already in the list.

Only with RESTART IDENTITY, and only the sequences owned by the truncated columns, which covers serial and identity columns. Without it the sequences carry on from where they were, so the first row after the reset gets the next unused number rather than 1.

It truncates the tables you named and every table that references them through a foreign key, directly or through another table, in any schema. Tables with no key into that set are left alone, so CASCADE on one parent is only a full reset when everything hangs off it. The line under the table list in the generator names what a partial selection would take with it.

Rolling the transaction back puts the sequence back with the rows, and on PostgreSQL 18 the next insert after such a rollback took the id it would have taken had none of it happened. That holds for RESTART IDENTITY only, since a sequence moved by hand inside the same transaction does not come back with it.

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.

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.

More tools