PostgreSQL: DELETE Statement

In this PostgreSQL post explains how to use the PostgreSQL DELETE statement with syntax and examples.

Description

The PostgreSQL DELETE statement is used to delete a single record or multiple records from a table in PostgreSQL.

Syntax

The syntax for the DELETE statement in PostgreSQL is:

DELETE FROM table
[WHERE conditions];

Parameters or Arguments

table

The table that you wish to delete records from.

WHERE conditions

Optional. The conditions that must be met for the records to be deleted. If no conditions are provided, then all of the records from the table will be deleted.

Note

• You do not need to list fields in the PostgreSQL DELETE statement since you are deleting the entire row from the table.

Example - With One condition

Let's look at a simple PostgreSQL DELETE query example, where we just have one condition in the DELETE statement.

For example:

DELETE FROM contacts
WHERE first_name = 'Sarah';

This PostgreSQL DELETE example would delete all records from the contacts table where the first_name is 'Sarah'.

You may wish to check for the number of rows that will be deleted. You can determine the number of rows that will be deleted by running the following SELECT statement before performing the delete.

SELECT count(*)
FROM contacts
WHERE first_name = 'Sarah';

Example - With Two conditions

Let's look at a PostgreSQL DELETE example, where we just have two conditions in the DELETE statement.

For example:

DELETE FROM contacts
WHERE first_name = 'Beth'
AND contact_id >= 400;

This PostgreSQL DELETE example would delete all records from the contacts table where the first_name is 'Beth' and the customer_id is greater than or equal to 400.

You may wish to check for the number of rows that will be deleted. You can determine the number of rows that will be deleted by calling the postgresql_info function or by running the following SELECT statement before performing the delete.

SELECT count(*)
FROM contacts
WHERE first_name = 'Beth'
AND contact_id >= 400;

Example - Using EXISTS Condition

You can also perform more complicated deletes.

You may wish to delete records in one table based on values in another table. Since you can't list more than one table in the PostgreSQL FROM clause when you are performing a delete, you can use the EXISTS clause.

For example:

DELETE FROM suppliers
WHERE EXISTS
  ( SELECT 1
    FROM customers
    WHERE customers.customer_id = suppliers.supplier_id
    AND customer_id  1000 );

This PostgreSQL DELETE example would delete all records in the suppliers table where there is a record in the customers table whose customer_id is less than 1000, and the customer_id matches the supplier_id.

You may wish to check for the number of rows that will be deleted. You can determine the number of rows that will be deleted by calling the postgresql_info function or by running the following SELECT statement before performing the delete.

SELECT COUNT(*) FROM suppliers
WHERE EXISTS
  ( SELECT 1
    FROM customers
    WHERE customers.customer_id = suppliers.supplier_id
    AND customer_id  1000 );