PostgreSQL DELETE statement

The PostgreSQL DELETE statement is used to remove one or more records from a table in PostgreSQL.

DELETE statement syntax in PostgreSQL

DELETE FROM tab
[WHERE conds];

Parameters and arguments of the statement

tab – The table from which you want to remove the entries.
WHERE conds – Optional. The conditions that must be met to delete records. If there are no conditions are provided, all records from the table will be deleted.

Note:

• You do not need to enumerate fields in PostgreSQL statement DELETE, because you remove the whole row from the table.

An example statement with one condition

Consider a simple example where we have only one condition in the DELETE operator.
For example:

DELETE FROM contacts
WHERE first_name = 'Freya';

This PostgreSQL DELETE example will remove all records from the contacts table, where first_name equals ‘Freya’.

You can define the number of lines to be deleted by executing the following SELECT operator before deleting.

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

An example of a statement with two conditions

Consider an example where we have only two conditions in a DELETE operator.
For example:

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

This PostgreSQL example DELETE will delete all records from the contacts table, where first_name has the value “Bony”, and the customer_id is greater than or equal to 400.

You can define the number of lines to be deleted by executing the following SELECT operator before deleting.

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

Example using EXISTS condition

You can also perform more complex deletions.
You can delete records in one table based on the values in another table. Since you can’t list more than one table in the PostgreSQL FROM statement when you perform deletions, you can use the EXISTS sentence.

For example:

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

This example will delete all entries in the supplier table that have an entry in the customer_id table that is less than 1000 and customer_id that corresponds to supplier_id.

You can define the number of rows to be deleted by calling the postgresql_info function or by executing the following SELECT statement before deleting.

SELECT COUNT(*) FROM suppls
WHERE EXISTS
( SELECT 1
FROM customers
WHERE customers.customer_id = suppls.suppl_id
AND customer_id < 1000 );

PostgreSQL: How to Delete Records | Course