PostgreSQL Delete Query | Delete Rows from Select in Postgres

Delete Query in PostgreSQL

The Delete Statement in PostgreSQL is used to delete either one or more records from a table. If you want to delete select rows from a table PostgreSQL allows you to combine the DELETE statement with the WHERE clause else it will delete all records.

In this PostgreSQL tutorial, you will learn:

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

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 will delete 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.

PostgreSQL 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:

PostgreSQL Delete Query with Two Conditions

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:

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.

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:

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 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:

PostgreSQL Delete Query using Exists Condition

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.

PostgreSQL Delete Row in pgAdmin

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

PostgreSQL Delete Row in pgAdmin

Step 5) Check if the Row is Deleted

Let us check whether the deletion was successful:

PostgreSQL Delete Row in pgAdmin

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.

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

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.

Delete Row in PostgreSQL using pgAdmin EXISTS Condition

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 in PostgreSQL using pgAdmin EXISTS Condition

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

Delete Row in PostgreSQL using pgAdmin EXISTS Condition

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