PostgreSQL Delete Query (Delete Rows from Select)

Postgres Delete Query Syntax

The DELETE statement can be defined by the below syntax:

[ 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

• with-query: the WITH clause allows us to reference one or more subqueries to be referenced by name in DELETE query.
• table-name: the name of the table from which records are to be deleted.
• alias: this is a substitute for the name of the target table.
• using-list: table expressions to allow columns from other tables to be used in WHERE clause.
• condition(s): optional. They 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 WHERE CURRENT OF condition. The lastly fetched row by this cursor will be deleted.
• output-expression: the expression to be processed and returned by DELETE statement after the deletion of every row.
• output-name: the name to be used for the returned column.

Note that since the DELETE statement deletes the entire row, you don’t need to specify the column names.

PostgreSQL Delete Query with One Condition

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

Price

Let us delete the record with an id of 4:

DELETE FROM Price
WHERE id = 4;

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

The row with an id of 4 has been deleted.

Delete Query with Two Conditions

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

Price:

Consider the example given below:

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

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

SELECT * FROM Price

This Returns the following:

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.

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

Book:

Price:

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 above command will delete from the Book table where there exists a record in the Price table with an id matching that of the Book table and the price being less than 250.

The Book table is now as follows:

The record with an id of 1 was deleted.

How To Delete Row in PostgreSQL using pgAdmin

Following are the steps to delete a row in PostgreSQL using pgAdmin:

With One condition

To accomplish the same through pgAdmin, do this:

Step 1) Login to your pgAdmin account

Open pgAdmin and Login 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.

Step 3) Type the Query

Type the below query in the query editor:

DELETE FROM Price
WHERE id = 4;

Step 4) Execute the Query

Click the Execute button

Step 5) Check if the Row is Deleted

Let us check whether the deletion was successful:

With Two conditions

To accomplish the same through pgAdmin, do this:

Step 1) Login to your pgAdmin account.

Step 2)

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

Step 3) Type the query in the query editor:

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

Step 4) Click the Execute button.

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

Using EXISTS Condition

To accomplish the same through pgAdmin, do this:

Step 1) Login to your pgAdmin account.

Step 2)

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

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.

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

Summary

• The DELETE statement is used for deleting one or more records from a table.
• To delete only select rows from a table, you can combine the DELETE statement with the WHERE clause.
• If the DELETE clause is used without the WHERE clause, it deletes all records from the table.
• table-name parameter allows you to add the name of the table from which records are to be deleted.
• We can use the DELETE statement with one condition, specified using the WHERE clause.
• The DELETE statement can also be used with two conditions specified in the WHERE clause. The two conditions should be joined using the AND operator.
• The EXISTS condition can help us delete records from a table based on the records of another table.

Download the Database used in this Tutorial