How to DELETE Rows in PostgreSQL

Galaxy Glossary

How do I safely delete rows in PostgreSQL?

DELETE permanently removes selected rows from a PostgreSQL table.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

What problem does the DELETE command solve?

The DELETE statement removes unwanted rows, freeing storage and keeping datasets accurate. Use it when soft-delete columns or archival tables are unnecessary.

How do I write a basic DELETE statement?

Start with DELETE FROM table_name WHERE condition;. The WHERE clause is mandatory for anything other than a full-table purge.

When should I omit the WHERE clause?

Only omit WHERE when you truly intend to empty the table.Always double-check by running a SELECT first.

Can I preview affected rows before deleting?

Yes. Execute the same condition with SELECT *. When satisfied, change SELECT to DELETE.

How can I delete using a JOIN?

Use USING in DELETE.It lets you reference another table, useful for cascade-like cleanups without foreign keys.

Should I return deleted data?

The RETURNING clause outputs deleted rows—ideal for audits or downstream logic.

Best practices for safe deletions?

Wrap deletions in a transaction, add indexes on filter columns, and log statements. Always test in staging first.

.

Why How to DELETE Rows in PostgreSQL is important

How to DELETE Rows in PostgreSQL Example Usage


-- Remove cancelled orders older than a year and return their IDs
DELETE FROM Orders
WHERE order_date < NOW() - INTERVAL '1 year'
  AND status = 'cancelled'
RETURNING id;

How to DELETE Rows in PostgreSQL Syntax


DELETE [FROM] <table_name>
[USING <other_table> [, ...]]
[WHERE <condition>]
[RETURNING * | column_list];

Common Mistakes

Mistake: Running DELETE without a WHERE clause in production. This wipes the entire table. Fix: Always add a WHERE and test with SELECT first.
Mistake: Forgetting an index on the filtered column, causing slow or blocked deletes. Fix: Create an index on columns used in WHERE before large deletions.

Frequently Asked Questions (FAQs)

Is DELETE the same as TRUNCATE?

No. TRUNCATE instantly removes all rows and can't use WHERE or RETURNING. DELETE is slower but flexible and transactional.

Will DELETE free disk space immediately?

Space becomes reusable for the same table, but disk files shrink only after VACUUM FULL or table rewrite.

How do I delete duplicate rows?

Use a CTE with ROW_NUMBER, then DELETE where row_number > 1.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie