DELETE expression

The DELETE expression deletes rows from the table. The condition for selecting the rows to be deleted can be specified through the WHERE expression.

The number of deleted rows is returned as the query result.

Instead of deleting or modifying rows in existing tables (expressions DELETE and UPDATE), it is recommended to create new tables based on existing tables with the necessary filters (expression CREATE TABLE) and truncate old ones if necessary (expression TRUNCATE).

Syntax

DELETE FROM <table_name>
    [ WHERE <condition> ]

Parameters

  • [ WHERE <condition> ]

    Specifies the condition to be used to select rows for deletion. If this parameter is omitted, all rows in the table are deleted, but the table is not deleted.

    Allowed operators inside the condition <condition>:

    • = equals

    • > greater than

    • < less than

    • in (…​) is included in the list

    • their combinations

Examples

Example 1

Create a table with numbers from 1 to 100 and delete all rows from it without additional conditions:

CREATE TABLE demo.numbers (numbers BIGINT);

INSERT INTO demo.numbers (numbers)
    SELECT unnest(generate_series(1,100));

DELETE FROM demo.numbers;
+-------+
| count |
+-------+
| 100   |
+-------+

Let’s check the contents of the table:

SELECT * FROM demo.numbers;
+---------+
| numbers |
+---------+
0 rows

Example 2

Create a table with numbers from 1 to 100 and delete rows with numbers less than 51 or greater than 55 from it:

CREATE TABLE demo.numbers (numbers BIGINT);

INSERT INTO demo.numbers (numbers)
    SELECT unnest(generate_series(1,100));

DELETE FROM demo.numbers
    WHERE numbers < 51 OR numbers > 55;
+-------+
| count |
+-------+
| 95    |
+-------+

Let’s check the contents of the table:

SELECT * FROM demo.numbers
+---------+
| numbers |
+---------+
| 51      |
+---------+
| 52      |
+---------+
| 53      |
+---------+
| 54      |
+---------+
| 55      |
+---------+

Example 3

Create a table with numbers from 1 to 10 and delete rows with numbers from the list (2,4,6,8,10,12) from it:

CREATE TABLE demo.numbers (numbers BIGINT);

INSERT INTO demo.numbers (numbers)
    SELECT unnest(generate_series(1,10));

DELETE FROM demo.numbers
    WHERE numbers IN (2,4,6,8,10,12);
+-------+
| count |
+-------+
| 5     |
+-------+

Let’s check the contents of the table:

SELECT * FROM demo.numbers;
+---------+
| numbers |
+---------+
| 1       |
+---------+
| 3       |
+---------+
| 5       |
+---------+
| 7       |
+---------+
| 9       |
+---------+