DELETE vs TRUNCATE vs DROP in PostgreSQL: Try the Difference

DELETE: remove only completed jobs

The first command deletes jobs whose status is done. RETURNING displays the IDs and task names actually removed. The following SELECT shows that build report, ID 2, remains queued.

Without WHERE, DELETE removes every row while preserving the table. Preview a production predicate with SELECT and review its scope before deleting. A transaction gives you a chance to inspect changes before committing; it is not a backup after commit.

DELETE processes rows and fires applicable DELETE triggers. It does not reset the identity sequence. Deleted row versions ordinarily need vacuuming before their space can be reused; ordinary VACUUM generally does not return that space to the operating system. See PostgreSQL's DELETE reference and routine vacuuming guide.

psql

DELETE FROM jobs WHERE status = 'done' RETURNING id, task;

SELECT id, task, status FROM jobs ORDER BY id;

TRUNCATE: empty the queue and restart numbering

The remaining queued job disappears. The table definition survives, and RESTART IDENTITY resets its owned sequence. Inserting new batch then returns ID 1. Plain TRUNCATE defaults to CONTINUE IDENTITY.

TRUNCATE avoids scanning individual rows and reclaims table storage immediately. It takes an ACCESS EXCLUSIVE lock, blocking concurrent access to the table. It fires TRUNCATE triggers, not DELETE triggers. It also has snapshot visibility differences from ordinary deletion; see the TRUNCATE reference.

Foreign keys matter even when the referencing table is empty: include referencing tables explicitly or the command is refused. TRUNCATE … CASCADE can empty those tables too. Review that scope before using it.

TRUNCATE TABLE jobs RESTART IDENTITY;

INSERT INTO jobs(task,status) VALUES ('new batch','queued') RETURNING id;

SELECT count(*) AS rows_after_restart FROM jobs;

ROLLBACK: undo a truncation before commit

BEGIN opens a transaction. TRUNCATE empties jobs, so the count inside the transaction is zero. ROLLBACK restores the new batch row.

PostgreSQL rolls back TRUNCATE and its optional identity restart when the transaction is rolled back. This experiment uses plain TRUNCATE to focus on row restoration.

Keep every statement of a transaction in the same database session. Closing the connection without committing rolls an open transaction back. In psql's default autocommit mode, statements outside an explicit transaction commit individually.

BEGIN;

TRUNCATE TABLE jobs;

SELECT count(*) AS rows_inside_transaction FROM jobs;

ROLLBACK;

SELECT id, task FROM jobs ORDER BY id;

DROP TABLE: remove the object, then restore it

DROP TABLE removes jobs, including its indexes, constraints and triggers. Instead of issuing a SELECT against a missing table, the experiment uses to_regclass: a missing relation resolves to NULL, so table_is_gone is true.

ROLLBACK restores the table and its row. The next two queries confirm that the object exists again and still contains new batch. Replacing ROLLBACK with COMMIT would make the drop persist.

DROP normally refuses dependent objects such as views or incoming foreign-key constraints. DROP CASCADE removes dependencies: a dependent view is removed, while a foreign-key dependency removes the constraint, not the referencing table. That differs from TRUNCATE CASCADE, which empties referencing tables. See the DROP TABLE reference.

Deleting data requires suitable DELETE privileges; truncation requires TRUNCATE privileges. Dropping a table requires ownership authority. Owning the lab table lets the student role perform all three without switching to a database administrator.

BEGIN;

DROP TABLE jobs;

SELECT to_regclass('public.jobs') IS NULL AS table_is_gone;

ROLLBACK;

SELECT to_regclass('public.jobs') IS NOT NULL AS table_is_back;

SELECT id, task FROM jobs ORDER BY id;