PostgreSQL DELETE Rows from Select

By : Juniper Willow Juniper Willow
Hours Updated

⚡ Smart Summary

PostgreSQL DELETE removes one or more rows from a table, and pairing it with a WHERE clause targets only the records that match a condition, while omitting WHERE clears every row in the table.

  • 📋 Syntax: DELETE FROM table WHERE condition removes matching rows, and RETURNING can echo the deleted data.
  • 🎯 One Condition: A single WHERE clause deletes only the rows that satisfy one test.
  • ➕ Two Conditions: Joining two tests with AND narrows the delete to precise rows.
  • 🔗 EXISTS Clause: EXISTS deletes rows in one table based on matching records in another table.
  • 🖥️ pgAdmin: The same delete queries run visually through the pgAdmin query editor.
  • 🤖 AI Safety: AI assistants add the correct WHERE clause and warn before full-table deletes.
Read More

PostgreSQL Delete Rows from Select

Delete Query in PostgreSQL

The DELETE statement in PostgreSQL is used to delete one or more records from a table. To delete only selected rows from a table, PostgreSQL allows you to combine the DELETE statement with the WHERE clause; otherwise, it deletes all records in the table.

PostgreSQL Delete Query Syntax

The DELETE statement can be defined using the syntax below:

[ WITH [ RECURSIVE ] with-query [, ...] ]
DELETE FROM [ ONLY ] table-name [ * ] [ [ AS ] alias ]
    [ USING using-list ]
    [ WHERE condition(s) | WHERE CURRENT OF cursor-name]
    [ RETURNING * | output-expression [ [ AS ] output-name] [, ...] ]

Parameters

The following parameters are available:

  • with-query: the WITH clause allows you to reference one or more subqueries by name inside the DELETE query.
  • table-name: the name of the table from which records are to be deleted.
  • alias: a substitute for the name of the target table.
  • using-list: table expressions that allow columns from other tables to be used in the WHERE clause.
  • condition(s): optional. These are the conditions that must be satisfied for records to be deleted. If this section is not provided, all table-name records will be deleted.
  • cursor-name: the cursor to be used in the WHERE CURRENT OF condition. The last row fetched by this cursor will be deleted.
  • output-expression: the expression to be processed and returned by the DELETE statement after every row is deleted.
  • output-name: the name to be used for the returned column.

Note that since the DELETE statement deletes the entire row, you do not need to specify the column names.

PostgreSQL Delete Query with One Condition

The DELETE statement can be used with a single condition, which is set using the WHERE clause. Consider the Price table with the following data:

Price:

PostgreSQL Delete Query with One Condition

Let us delete the record with an id of 4:

DELETE FROM Price
WHERE id = 4;

The above command deletes the records in which the id is 4. Let us confirm whether the deletion was successful:

PostgreSQL Delete Query with One Condition

The row with an id of 4 has been deleted.

Delete Query with Two Conditions

The PostgreSQL DELETE statement can also take two conditions. The two conditions should be joined using the AND operator. We will use the following table:

Price:

PostgreSQL Delete Query with Two Conditions

Consider the example given below:

DELETE FROM Price
WHERE id = 3
AND price = 300;

In the above command, we are deleting the row in which the id is 3 and the price is 300. We can now query the table:

SELECT * FROM Price;

This returns the following:

PostgreSQL Delete Query with Two Conditions

The record with an id of 3 and a price of 300 was deleted.

PostgreSQL Delete Query using EXISTS Condition

With the EXISTS condition, you can make the DELETE more complex. Sometimes there may be a need to delete records in one table based on records in another table.

Because the FROM clause does not allow you to list records from more than one table when performing a delete, the EXISTS clause becomes very useful. We have the following two tables:

Book:

PostgreSQL Delete Query using EXISTS Condition

Price:

PostgreSQL Delete Query using EXISTS Condition

We can then run the following query:

DELETE FROM Book
WHERE EXISTS
  (SELECT 1
    FROM Price
    WHERE Price.id = Book.id
    AND price < 250 );

The command deletes rows from the Book table that have a matching id in the Price table with a price below 250.

The Book table is now as follows:

PostgreSQL Delete Query using EXISTS Condition

The record with an id of 1 was deleted.

How To Delete a Row in PostgreSQL using pgAdmin

The same delete queries can also be run visually through the pgAdmin interface, following the steps below:

RELATED ARTICLES

With One Condition

Step 1) Login to your pgAdmin account. Open pgAdmin and log in to your account using your credentials.

Step 2) Create a Demo Database.

  1. From the navigation bar on the left, click Databases.
  2. Click Demo.

Delete Row in PostgreSQL using pgAdmin

Step 3) Type the below query in the query editor:

DELETE FROM Price
WHERE id = 4;

Step 4) Click the Execute button.

Delete Row in PostgreSQL using pgAdmin

Step 5) Let us check whether the row was deleted:

Delete Row in PostgreSQL using pgAdmin

With Two Conditions

Step 1) Login to your pgAdmin account.

Step 2)

  1. From the navigation bar on the left, click Databases.
  2. Click Demo.

Delete Row in PostgreSQL using pgAdmin

Step 3) Type the query in the query editor:

DELETE FROM Price
WHERE id = 3
AND price = 300;

Step 4) Click the Execute button.

Delete Row in PostgreSQL using pgAdmin

Step 5) Let us check whether the deletion was successful:

Delete Row in PostgreSQL using pgAdmin

Using EXISTS Condition

Step 1) Login to your pgAdmin account.

Step 2)

  1. From the navigation bar on the left, click Databases.
  2. Click Demo.

Delete Row using EXISTS Condition in pgAdmin

Step 3) Type the query in the query editor:

DELETE FROM Book
WHERE EXISTS
  (SELECT 1
    FROM Price
    WHERE Price.id = Book.id
    AND price < 250 );

Step 4) Click the Execute button.

Delete Row using EXISTS Condition in pgAdmin

Step 5) Let us check whether the deletion was successful:

Delete Row using EXISTS Condition in pgAdmin

Download the Database used in this Tutorial

FAQs

TRUNCATE removes every row instantly and resets storage, but ignores WHERE and per-row triggers. DELETE removes selected rows using a WHERE clause, fires row triggers, and can be rolled back inside a transaction.

Add a USING clause: DELETE FROM member USING denylist WHERE member.phone = denylist.phone. It joins the second table and deletes matching rows, giving the same result as EXISTS with clearer join-style syntax.

RETURNING echoes columns from the rows just deleted in the same statement. Write DELETE FROM Price WHERE id = 4 RETURNING *, and PostgreSQL returns the removed data so applications can log or confirm the change.

Yes. Wrap the DELETE in a transaction with BEGIN, then run ROLLBACK to undo it or COMMIT to keep it. Until you commit, the removed rows can be restored, which protects against mistaken deletions.

Keep one copy per group using the hidden ctid column: DELETE FROM Price a USING Price b WHERE a.ctid < b.ctid AND a.id = b.id. This removes duplicate rows while retaining a single record.

Run the same filter as a SELECT first, or open a transaction with BEGIN and use RETURNING to inspect the affected rows. Reviewing the WHERE result before COMMIT prevents deleting more rows than intended.

AI assistants translate plain-English rules into a DELETE with the correct WHERE clause, suggest EXISTS or USING for cross-table deletes, and flag statements missing a condition that would clear the whole table.

Yes. An AI Copilot reviews each DELETE, warns when no WHERE clause is present, and recommends a transaction or a RETURNING preview, reducing the risk of an accidental full-table delete.

Summarize this post with: