DELETE

Use the DELETE command to permanently remove rows from a table.

• Call FLUSH after DELETE to persist the changes to storage. This ensures that the changes are committed and visible for subsequent reads.
• If you only need the DELETE to wait until it is included in a successful checkpoint, you can enable DML_WAIT_PERSISTENCE. This does not provide the same immediate read-after-write visibility as FLUSH.

Syntax

DELETE FROM table_name
WHERE condition
[ RETURNING col_name ];

Parameters

Parameter or clause	Description
table_name	The table where you want to remove records.
WHERE condition	Specify which rows you want to remove using an expression that returns a boolean value. Rows for which this expression returns true will be removed. If you omit the WHERE clause, all rows of records in the table will be deleted but the table structure will be kept.
RETURNING	Returns the values of any column based on each deleted row.

Example

The taxi_trips table has three records:

SELECT * FROM taxi_trips;

 id | distance |    city
----+----------+-------------
  1 |       16 | Yerba Buena
  2 |       23 | New York
  3 |        6 | Chicago
(3 rows)

The following statement removes the record with id 3 from the table. Also, it returns the value of id for the deleted row.

DELETE FROM taxi_trips
WHERE id = 3
RETURNING id;

The following statement removes all rows from the table.

DELETE FROM taxi_trips

Let’s see the result.

SELECT * FROM taxi_trips;

 id | distance | city
----+----------+------
(0 rows)