How to Delete Data in PostgreSQL

Removing data is just as much a part of everyday database work as adding it — cleaning up test records, removing a cancelled order, honoring a user’s account deletion request. PostgreSQL gives you a few different tools for this depending on exactly what you need to remove, and each comes with its own behavior and risks. This guide walks through DELETE, how it compares to TRUNCATE, how it interacts with foreign keys, and the habits that keep this kind of operation safe.

Basic DELETE Syntax

DELETE FROM table_name
WHERE condition;

A concrete example:

DELETE FROM customers
WHERE id = 5;

This removes exactly one row — the customer with id = 5 — from the customers table. As with UPDATE, the WHERE clause is what limits the damage. Omit it, and PostgreSQL deletes every row in the table without hesitation:

DELETE FROM customers;

This empties the entire customers table. It’s syntactically valid and PostgreSQL will run it exactly as written, so this is a command worth double-checking before you hit enter.

Confirming What You’re About to Delete

The safest habit with any DELETE is to first run the same condition as a SELECT, to see exactly which rows will be affected:

SELECT * FROM customers WHERE id = 5;

Only once you’ve confirmed that returns exactly the rows you expect should you convert it into a DELETE.

Deleting Multiple Rows

DELETE FROM orders
WHERE status = 'cancelled' AND created_at < NOW() - INTERVAL '1 year';

This removes every order that’s been cancelled for more than a year — a common cleanup pattern for keeping historical tables from growing indefinitely with data nobody needs anymore.

Returning Deleted Rows with RETURNING

Like INSERT and UPDATE, DELETE also supports RETURNING, giving you back the data from the rows that were just removed:

DELETE FROM orders
WHERE id = 205
RETURNING *;

This is genuinely useful — it lets your application confirm exactly what was deleted, log it for audit purposes, or display a confirmation message, all without a separate query run beforehand.

DELETE FROM sessions
WHERE expires_at < NOW()
RETURNING id, user_id;

This deletes expired sessions and returns the IDs and associated user IDs of everything removed, which could be logged or used to trigger follow-up actions.

Deleting Based on Data in Another Table

Sometimes the condition for deletion depends on related data in a different table. PostgreSQL supports this with DELETE ... USING:

DELETE FROM orders
USING customers
WHERE orders.customer_id = customers.id
  AND customers.status = 'banned';

This removes all orders belonging to customers whose status is 'banned', joining across the two tables to determine which rows in orders qualify.

An equivalent approach using a subquery, which some people find more readable:

DELETE FROM orders
WHERE customer_id IN (
    SELECT id FROM customers WHERE status = 'banned'
);

Both approaches produce the same result — which one to use is mostly a matter of style and, on very large tables, sometimes query performance (worth checking with EXPLAIN if it matters for your case).

Foreign Keys and DELETE Behavior

If a row you’re trying to delete is referenced by rows in another table through a foreign key, PostgreSQL’s behavior depends on how that foreign key was defined.

Default Behavior: Restrict

By default, if a foreign key exists with no special action defined, PostgreSQL blocks the delete:

DELETE FROM customers WHERE id = 5;

ERROR:  update or delete on table "customers" violates foreign key constraint "orders_customer_id_fkey" on table "orders"
DETAIL:  Key (id)=(5) is still referenced from table "orders".

This protects your data — it prevents you from leaving orders rows pointing to a customer that no longer exists.

ON DELETE CASCADE

If the foreign key was defined with ON DELETE CASCADE, deleting the parent row automatically deletes all related child rows too:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id) ON DELETE CASCADE
);

With this in place, DELETE FROM customers WHERE id = 5 would also automatically delete every order belonging to that customer. This is convenient, but it’s worth being fully aware of when it’s set up in your schema — cascading deletes can remove far more data than a quick glance at the command suggests.

ON DELETE SET NULL

Another option is to have the foreign key column set to NULL rather than deleting the related rows:

customer_id INTEGER REFERENCES customers(id) ON DELETE SET NULL

This is useful when you want to keep historical records (like orders) even after the referenced customer is gone, just without a valid link back to them.

Checking Existing Foreign Key Behavior

To see how foreign keys on a table are currently configured:

SELECT
    conname AS constraint_name,
    confdeltype AS delete_action
FROM pg_constraint
WHERE conrelid = 'orders'::regclass AND contype = 'f';

The confdeltype values map to: a (no action / restrict), c (cascade), n (set null), d (set default), r (restrict).

DELETE vs. TRUNCATE

If your goal is to remove all rows from a table, TRUNCATE is almost always the better choice over an unconditional DELETE:

TRUNCATE TABLE orders;

Key differences:

• TRUNCATE is dramatically faster on large tables, since it deallocates entire data pages rather than removing rows one at a time.
• TRUNCATE resets any SERIAL/identity sequence associated with the table by default, so new rows start counting from 1 again (this can be avoided with TRUNCATE TABLE orders RESTART IDENTITY to control it, or CONTINUE IDENTITY explicitly to preserve the sequence).
• TRUNCATE doesn’t fire row-level triggers the way DELETE does (though it does fire statement-level triggers if defined).
• DELETE can be filtered with WHERE; TRUNCATE always removes everything.
• TRUNCATE can also cascade to dependent tables if you add CASCADE, similar in spirit to DROP TABLE CASCADE.

If you need to remove only some rows, DELETE is the only option. If you’re clearing an entire table, TRUNCATE is faster and generally the right tool.

Deleting Duplicate Rows

A common real-world need is cleaning up duplicate rows that shouldn’t exist, often after a data import gone wrong. Here’s a reliable pattern using ctid (PostgreSQL’s internal row identifier):

DELETE FROM customers a
USING customers b
WHERE a.ctid < b.ctid
  AND a.email = b.email;

This keeps the row with the highest ctid for each duplicate email and deletes the rest. An alternative, often clearer approach uses window functions:

DELETE FROM customers
WHERE id IN (
    SELECT id FROM (
        SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
        FROM customers
    ) sub
    WHERE rn > 1
);

This keeps the earliest row (lowest id) for each duplicated email and removes the rest.

Practical Examples

Removing a single customer record on request

DELETE FROM customers
WHERE id = 42
RETURNING id, email;

Cleaning up expired password reset tokens

DELETE FROM password_reset_tokens
WHERE expires_at < NOW();

Removing test data before a demo

DELETE FROM orders WHERE customer_id IN (
    SELECT id FROM customers WHERE email LIKE '%@test.example.com'
);

Bulk clearing an entire logs table

TRUNCATE TABLE application_logs RESTART IDENTITY;

Common Use Cases

GDPR / user-requested data deletion. Applications handling personal data often need to fully remove a user’s records on request, sometimes cascading across many related tables.

Expiring temporary data. Session tokens, password reset links, and similar time-limited records are routinely deleted once expired, often via a scheduled job.

Cleaning up test or demo data. Development and staging environments regularly need bulk deletion of records that shouldn’t persist.

Removing orphaned or invalid records. Data quality maintenance sometimes involves deleting rows that fail validation rules introduced after the data was originally inserted.

Troubleshooting Common Errors

ERROR: update or delete on table violates foreign key constraint. Covered above — either delete the dependent rows first, add ON DELETE CASCADE to the foreign key if that behavior is genuinely desired, or set the referencing column to NULL manually before deleting the parent row.

Deleted more rows than intended. If caught immediately and still inside an open transaction, ROLLBACK undoes it. If already committed, recovery depends on having a backup — this is exactly why testing your WHERE clause with SELECT first matters so much.

Delete runs but affects zero rows. The WHERE condition didn’t match anything — confirm with the equivalent SELECT that the data you expect actually exists and matches your filter.

Slow deletes on large tables. Deleting a large number of rows in one statement can hold locks for a long time and bloat the table (PostgreSQL doesn’t immediately reclaim space from deleted rows — that’s handled by VACUUM). For very large deletions, consider batching:

DELETE FROM logs WHERE id IN (
    SELECT id FROM logs WHERE created_at < NOW() - INTERVAL '1 year' LIMIT 10000
);

Run this repeatedly until no rows remain, which keeps lock duration and transaction size manageable.

Best Practices

• Test with SELECT first, always, especially before any DELETE without a highly specific WHERE clause.
• Wrap meaningful deletes in a transaction so you can verify the result before committing:

BEGIN;
DELETE FROM orders WHERE status = 'cancelled' AND created_at < NOW() - INTERVAL '1 year';
-- check row count / RETURNING output looks right
COMMIT; -- or ROLLBACK;

• Understand your foreign key cascade behavior before deleting parent records — know whether you’re about to trigger a cascade across related tables.
• Use TRUNCATE instead of unconditional DELETE when clearing an entire table, for both speed and cleaner sequence handling.
• Batch large deletions rather than removing millions of rows in a single statement, to avoid long lock times and excessive transaction size.
• Run VACUUM (or rely on autovacuum, which is enabled by default) after large deletes to reclaim disk space and keep query planner statistics accurate.
• Keep backups current, particularly before any deletion affecting production data at scale.

Soft Deletes as an Alternative to DELETE

In many applications, permanently removing data isn’t actually the goal — retaining a history while hiding records from normal views is. This pattern is called a “soft delete,” and it’s implemented with a simple boolean or timestamp column rather than an actual DELETE:

ALTER TABLE customers ADD COLUMN deleted_at TIMESTAMPTZ;

UPDATE customers
SET deleted_at = NOW()
WHERE id = 42;

Application queries then filter out soft-deleted records:

SELECT * FROM customers WHERE deleted_at IS NULL;

Soft deletes trade simplicity for flexibility — they let you “undelete” records, maintain referential integrity with historical data (like old orders still referencing a “deleted” customer), and support audit requirements. The trade-off is that every query touching that table now needs to remember to filter out soft-deleted rows, which is easy to forget and a common source of subtle bugs. Some teams handle this by creating a view that automatically applies the filter:

CREATE VIEW active_customers AS
SELECT * FROM customers WHERE deleted_at IS NULL;

Deleting with LIMIT-Like Batching

Just like UPDATE, PostgreSQL’s DELETE doesn’t support a direct LIMIT clause. For batched deletion of large amounts of data, the same subquery pattern applies:

DELETE FROM logs
WHERE id IN (
    SELECT id FROM logs
    WHERE created_at < NOW() - INTERVAL '1 year'
    LIMIT 5000
);

Running this repeatedly (in a loop, either in application code or a DO block with intermediate commits) until it deletes zero rows is a much gentler approach on a large, actively-used table than a single massive DELETE statement, which would hold a lock and generate a huge burst of write-ahead log activity all at once.

Deleting and Reclaiming Disk Space with VACUUM

An important detail about DELETE that surprises newcomers: deleting rows doesn’t immediately shrink the table’s file size on disk. PostgreSQL’s MVCC architecture marks deleted rows as no longer visible but doesn’t physically remove them right away — that cleanup work is handled by VACUUM.

VACUUM ANALYZE orders;

VACUUM reclaims space for reuse within the table (though it generally doesn’t shrink the file size back to the operating system without VACUUM FULL, which is a much heavier operation that locks the table). ANALYZE updates the query planner’s statistics, which matters after a large delete since the table’s row count and data distribution have changed.

For a genuinely large one-time cleanup where reclaiming disk space back to the OS matters:

VACUUM FULL orders;

This rewrites the entire table into a new, compact file, but it requires an exclusive lock for the duration, so it should be run during a maintenance window on tables of meaningful size, not casually against a live production table during peak hours.

In most day-to-day situations, you don’t need to run VACUUM manually at all — PostgreSQL’s autovacuum process handles this automatically in the background. It’s worth knowing about mainly for understanding disk usage after large deletes, and for the rare cases where autovacuum genuinely needs a manual nudge or tuning.

Deleting Rows That Match a Pattern

Combining DELETE with LIKE or ILIKE for cleaning up test or junk data:

DELETE FROM customers
WHERE email ILIKE '%+test%@%';

This is a fairly common pattern for cleaning up test accounts created with a recognizable email convention (like user+test@example.com), a technique some teams use deliberately to make later cleanup easy.

Cascading Deletes Across Several Levels

ON DELETE CASCADE chains through multiple levels of relationships automatically. Consider three related tables:

CREATE TABLE customers (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id) ON DELETE CASCADE
);
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE
);

Deleting a single customer here automatically deletes all of their orders, and deleting those orders automatically deletes all of the associated order items — a single DELETE FROM customers WHERE id = 5 can ripple through and remove a potentially large amount of related data. This is powerful, but it’s also exactly the kind of thing worth testing carefully (in a transaction, or against staging data) before relying on it in production, since the scope of what gets removed isn’t obvious just from looking at the single DELETE statement.

Frequently Asked Questions

Is DELETE slower than TRUNCATE? Yes, generally — DELETE processes and logs each row individually (which also allows triggers to fire per row and supports WHERE filtering), while TRUNCATE deallocates entire data pages at once. For clearing an entire table, TRUNCATE is typically dramatically faster, especially on large tables.

Can I undo a DELETE after it’s been committed? Not through SQL alone. If caught before committing, ROLLBACK reverses it completely. After committing, your only path back is restoring from a backup — this is exactly why testing with SELECT first and using transactions for meaningful deletes matters so much.

Does DELETE reset auto-incrementing ID sequences? No — even if you delete every row in a table, the underlying sequence used for SERIAL/BIGSERIAL columns keeps its current value, so the next inserted row continues from where the sequence left off rather than starting back at 1. If you specifically want the sequence reset, that requires TRUNCATE TABLE ... RESTART IDENTITY or manually running ALTER SEQUENCE ... RESTART WITH 1.

What’s the safest way to test a risky DELETE before committing to it? Wrap it in an explicit transaction, run it, check the RETURNING output or affected row count, and only COMMIT once you’ve confirmed the results match your expectations — ROLLBACK if anything looks off.

Wrapping Up

DELETE is permanent the moment it’s committed, which makes the habits around it — checking with SELECT first, wrapping meaningful operations in transactions, understanding your foreign key cascade behavior, and batching large deletions — genuinely worth building into muscle memory rather than treating as optional caution. Combined with TRUNCATE for full-table clears, soft deletes where retaining history matters, and thoughtful foreign key design for cascading behavior, you’ve now got the full set of tools PostgreSQL offers for managing the lifecycle of your data, from creation through to removal.