PostgreSQL TRUNCATE PostgreSQL · Triggers · TRUNCATE

PL/pgSQL TRUNCATE Triggers

Deep dive on TRUNCATE triggers — DELETE-vs-TRUNCATE comparison, the statement-level constraints (no NEW/OLD, no FOR EACH ROW), and patterns: archive-before-truncate (BEFORE TRUNCATE can still SELECT), refuse-truncate (RAISE EXCEPTION), reset-summary-after-truncate (AFTER TRUNCATE on empty table), and a unified DELETE+TRUNCATE audit pattern via TG_OP branching. Critical correction: RAISE NOTICE does not abort — must use RAISE EXCEPTION.

TRUNCATE Triggers in Depth

TRUNCATE is PostgreSQL's bulk-delete shortcut: instead of removing rows one by one, it re-initializes the table's storage in constant time. That speed comes at a cost — TRUNCATE bypasses the row-by-row mechanics that DELETE relies on, including DELETE triggers.

If your data integrity story depends on triggers (audit logs, derived counts, notifications), TRUNCATE will silently break it unless you also add a TRUNCATE trigger. This page goes deeper on the TRUNCATE trigger model — how it's different from row-level triggers, what it can and can't do, and the patterns it's good for.

Property	DELETE	TRUNCATE
Speed on a 100M-row table	Hours	Seconds
WAL volume	One log entry per row	Minimal — single statement
VACUUM needed afterward	Yes — leaves dead rows	No — file replaced wholesale
Identity sequences reset	No	Optional via `RESTART IDENTITY`
Foreign-key cascade	Per-row, fires triggers	Whole-table, with optional `CASCADE` to other tables
Triggers fired	BEFORE/AFTER DELETE per row	BEFORE/AFTER TRUNCATE per statement
OLD record	Has the deleted row	Not available

TRUNCATE Trigger Constraints

The TRUNCATE trigger model has rules that don't apply to other event types:

Statement-level only. FOR EACH ROW is a syntax error for TRUNCATE triggers. There's no row context — the operation works on the whole table at once.
No NEW or OLD. Per-row records don't exist. Inside a TRUNCATE trigger function, referencing them raises an error.
TG_OP is 'TRUNCATE'. Useful when one function handles multiple event types.
RETURN NULL is the convention. Statement-level triggers' return values are ignored, but the function still has to RETURN something.
Cannot be defined on views. TRUNCATE doesn't apply to views; only tables.

CREATE [OR REPLACE] FUNCTION trigger_function_name() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN -- TG_OP, TG_TABLE_NAME, TG_TABLE_SCHEMA, etc. are available -- NEW and OLD are NOT available RETURN NULL; END; $$; CREATE TRIGGER trigger_name {BEFORE | AFTER} TRUNCATE ON table_name FOR EACH STATEMENT EXECUTE FUNCTION trigger_function_name();

Sample Setup

PL/pgSQL — Setup

DROP TABLE IF EXISTS truncate_audit;
DROP TABLE IF EXISTS orders_archive;
DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
    order_id    SERIAL PRIMARY KEY,
    customer    VARCHAR(100) NOT NULL,
    total       NUMERIC(10, 2) NOT NULL,
    placed_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders_archive (
    archive_id   SERIAL PRIMARY KEY,
    order_id     INT,
    customer     VARCHAR(100),
    total        NUMERIC(10, 2),
    placed_at    TIMESTAMP,
    archived_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE truncate_audit (
    id           SERIAL PRIMARY KEY,
    schema_name  TEXT,
    table_name   TEXT,
    row_count    INT,
    truncated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    truncated_by TEXT      DEFAULT CURRENT_USER
);

INSERT INTO orders (customer, total) VALUES
    ('Alice',   125.50),
    ('Bob',      89.99),
    ('Charlie',  210.00),
    ('Diana',    47.25),
    ('Eve',     333.33);

Examples

Example 1 — BEFORE TRUNCATE: Archive Then Allow

The most useful TRUNCATE pattern: archive every row to an archive table before the truncate completes. The trigger runs before the storage is wiped, so it can SELECT from the original table:

PL/pgSQL — archive on truncate

CREATE OR REPLACE FUNCTION archive_orders_before_truncate()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    moved_count INT;
BEGIN
    -- Copy all rows to the archive table
    INSERT INTO orders_archive (order_id, customer, total, placed_at)
    SELECT order_id, customer, total, placed_at
    FROM   orders;

    GET DIAGNOSTICS moved_count = ROW_COUNT;

    -- Log the truncate event
    INSERT INTO truncate_audit (schema_name, table_name, row_count)
    VALUES (TG_TABLE_SCHEMA, TG_TABLE_NAME, moved_count);

    RAISE NOTICE 'Archived % orders before truncating %.%',
                 moved_count, TG_TABLE_SCHEMA, TG_TABLE_NAME;

    RETURN NULL;
END;
$$;

CREATE TRIGGER orders_archive_on_truncate
    BEFORE TRUNCATE ON orders
    FOR EACH STATEMENT
    EXECUTE FUNCTION archive_orders_before_truncate();

Example 2 — Run the TRUNCATE

PL/pgSQL — truncate and verify

TRUNCATE TABLE orders;

-- Original is empty
SELECT count(*) AS orders_remaining FROM orders;

-- Archive has the data
SELECT * FROM orders_archive ORDER BY order_id;

-- Audit recorded the event
SELECT * FROM truncate_audit;

OUTPUT

psql — archived

postgres=#TRUNCATE TABLE orders;

NOTICE: Archived 5 orders before truncating public.orders

TRUNCATE TABLE

postgres=#SELECT count(*) AS orders_remaining FROM orders;

orders_remaining

------------------

(1 row)

postgres=#SELECT * FROM orders_archive ORDER BY order_id;

------------+----------+----------+--------+-----------+-------------

1 | 1 | Alice | 125.50 | ... | ...

2 | 2 | Bob | 89.99 | ... | ...

3 | 3 | Charlie | 210.00 | ... | ...

4 | 4 | Diana | 47.25 | ... | ...

5 | 5 | Eve | 333.33 | ... | ...

(5 rows)

The orders table is empty, but every row was archived first. The audit table records the event. This is the pattern to use when business rules say "we can wipe but never lose data."

Example 3 — BEFORE TRUNCATE: Outright Refuse

Sometimes the answer is "no, never." Reset the example with a refusal trigger instead:

PL/pgSQL — refuse truncate

DROP TRIGGER IF EXISTS orders_archive_on_truncate ON orders;

CREATE OR REPLACE FUNCTION refuse_orders_truncate()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    RAISE EXCEPTION 'TRUNCATE on % is not permitted in production. Use DELETE with a WHERE clause.',
                    TG_TABLE_NAME;
    RETURN NULL;
END;
$$;

CREATE TRIGGER orders_no_truncate
    BEFORE TRUNCATE ON orders
    FOR EACH STATEMENT
    EXECUTE FUNCTION refuse_orders_truncate();

-- Test
TRUNCATE TABLE orders;

OUTPUT

psql — refused

postgres=#TRUNCATE TABLE orders;

ERROR: TRUNCATE on orders is not permitted in production. Use DELETE with a WHERE clause.

CONTEXT: PL/pgSQL function refuse_orders_truncate() line 3 at RAISE

⚠️ Use RAISE EXCEPTION, not RAISE NOTICE. A NOTICE just prints a message — the truncate would still run. To genuinely abort, you must raise an actual exception. The original tutorial got this wrong: it used NOTICE and then claimed in the explanation that the trigger "raises an exception that aborts the operation" — those are two different things. NOTICE doesn't abort.

Example 4 — AFTER TRUNCATE: Reset Counters

AFTER TRUNCATE fires after the table has been emptied. Useful for resetting derived counts, cached aggregates, or related summary tables:

PL/pgSQL — reset summary after truncate

DROP TRIGGER IF EXISTS orders_no_truncate ON orders;

-- Imagine we maintain a summary table elsewhere
CREATE TABLE IF NOT EXISTS orders_summary (
    metric_name VARCHAR(50) PRIMARY KEY,
    value       NUMERIC
);

INSERT INTO orders_summary (metric_name, value) VALUES
    ('total_count',   5),
    ('total_revenue', 805.07)
ON CONFLICT (metric_name) DO UPDATE SET value = EXCLUDED.value;

CREATE OR REPLACE FUNCTION reset_orders_summary()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE orders_summary
    SET    value = 0
    WHERE  metric_name IN ('total_count', 'total_revenue');

    RAISE NOTICE 'Reset orders summary after truncate';
    RETURN NULL;
END;
$$;

CREATE TRIGGER orders_after_truncate_reset
    AFTER TRUNCATE ON orders
    FOR EACH STATEMENT
    EXECUTE FUNCTION reset_orders_summary();

TRUNCATE TABLE orders;
SELECT * FROM orders_summary;

OUTPUT

psql — summary reset

postgres=#TRUNCATE TABLE orders;

NOTICE: Reset orders summary after truncate

TRUNCATE TABLE

postgres=#SELECT * FROM orders_summary;

metric_name | value

----------------+-------

total_count | 0

total_revenue | 0

(2 rows)

Example 5 — One Function for DELETE and TRUNCATE

If your audit logic should run for both DELETE and TRUNCATE, you need two triggers (one for each event), but they can share a function that branches on TG_OP:

PL/pgSQL — unified audit function

CREATE OR REPLACE FUNCTION audit_data_loss()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        -- Per-row delete: log the specific row
        INSERT INTO truncate_audit (schema_name, table_name, row_count)
        VALUES (TG_TABLE_SCHEMA, TG_TABLE_NAME || ' (DELETE id ' || OLD.order_id || ')', 1);
        RETURN OLD;

    ELSIF TG_OP = 'TRUNCATE' THEN
        -- Whole-table truncate: log the bulk event
        INSERT INTO truncate_audit (schema_name, table_name, row_count)
        VALUES (TG_TABLE_SCHEMA, TG_TABLE_NAME || ' (TRUNCATE)',
                (SELECT count(*) FROM orders));
        RETURN NULL;
    END IF;

    RETURN NULL;
END;
$$;

-- Two triggers, one shared function
CREATE TRIGGER orders_delete_audit
    AFTER DELETE ON orders
    FOR EACH ROW
    EXECUTE FUNCTION audit_data_loss();

CREATE TRIGGER orders_truncate_audit
    BEFORE TRUNCATE ON orders   -- BEFORE so we can still count rows
    FOR EACH STATEMENT
    EXECUTE FUNCTION audit_data_loss();

This pattern fills the audit-on-DELETE gap that TRUNCATE creates. The DELETE trigger handles individual row removals; the TRUNCATE trigger handles the bulk event. Both end up in the same audit table.

BEFORE vs AFTER for TRUNCATE

Goal	Use
Refuse the TRUNCATE entirely	BEFORE — RAISE EXCEPTION
Archive rows before they're wiped	BEFORE — SELECT from the table while it still has data
Audit the event (when, by whom)	Either BEFORE or AFTER — both have access to TG_TABLE_NAME and TG_OP
Reset summary tables, derived counts	AFTER — table is already empty; safe to write zero values
Notify downstream systems	AFTER — fire only if the truncate actually happened

💡 BEFORE TRUNCATE can still see the rows. The truncate hasn't run yet when BEFORE fires, so you can SELECT from the table to capture data, count rows, or compute aggregates. AFTER TRUNCATE sees an empty table.

Best Practices

TRUNCATE triggers must be statement-level. No FOR EACH ROW, no NEW or OLD.
Use RAISE EXCEPTION to abort, not RAISE NOTICE. NOTICE only prints; it doesn't stop execution.
Plan for TRUNCATE in your audit strategy. DELETE triggers don't fire on TRUNCATE — you need separate TRUNCATE triggers if audit must be complete.
BEFORE TRUNCATE is the right place to archive data. The rows are still there; capture them before they're gone.
AFTER TRUNCATE is the right place to reset derived state. Counts, summaries, caches that should reflect the now-empty table.
Use TG_OP to share functions between DELETE and TRUNCATE. One function with branching logic, two trigger definitions, complete coverage.
Beware of TRUNCATE ... CASCADE — it truncates other tables linked by FK, and each of those may fire its own TRUNCATE triggers. Easy to underestimate the blast radius.

Conclusion

TRUNCATE triggers fire on TRUNCATE TABLE — a fast bulk-delete that bypasses per-row DELETE triggers.
They are statement-level only: no FOR EACH ROW, no NEW, no OLD. TG_OP is 'TRUNCATE'.
BEFORE TRUNCATE can still SELECT from the table — useful for archiving data before the wipe.
AFTER TRUNCATE sees the table empty — useful for resetting derived counts and summary tables.
Use RAISE EXCEPTION, not RAISE NOTICE, to abort a TRUNCATE you don't want to allow.
If audit logging matters, you need a TRUNCATE trigger separately from your DELETE trigger — they cover different events.
One trigger function can serve both DELETE and TRUNCATE if it branches on TG_OP; you'll still need two trigger definitions.