PostgreSQL DELETE statement

This article describes the basic form of the DELETE statement and how to use the DELETE statement to delete rows from a table.

In PostgreSQL, The DELETE statement is used to delete rows that meet specified conditions or all rows from a table.

PostgreSQL DELETE syntax

To delete rows from a table in a PostgreSQL database, use the DELETE statement.

DELETE FROM table_name
[where_clause]
[RETURNING expr];

Explanation:

  • After the DELETE FROM keyword is the name of the table from which to delete rows.

  • The where_clause is WHERE clause. Here you can use conditions to specify which rows in the table to delete.

  • The WHERE clauses are optional. If no WHERE clause specified, all rows in the table will be deleted.

  • The RETURNING clauses are optional. It is used to return information about deleted rows.

    The expr can be a column name or an expression. Use commas to separate multiple columns or expressions. You can also use * to represent all columns in a table.

    If no RETURNING clause specified, the DELETE statement returns the number of rows deleted.

If you want to clear a table more efficiently, use the TRUNCATE TABLE statement.

PostgreSQL DELETE statement example

We will use the tables in the Sakila sample database for demonstration, please install the Sakila sample database in PostgreSQL first.

To prevent data loss, we will create a table film_copy, which is a copy of the table film.

CREATE TABLE film_copy AS SELECT * FROM film;

film_copy and film have the same columns and rows.

Use PostgreSQL DELETE to delete rows that satisfy a condition from a table

To delete a film whose film_id is 1 from the film_copy table, use the following statement:

DELETE FROM film_copy
WHERE film_id = 1;

DELETE 1

The statement returns 1 to indicate that 1 row has been deleted.

To delete films whose film_id is 2, 3 or 4 from the film_copy table, use the following statement:

DELETE FROM film_copy
WHERE film_id in (2, 3, 4);

DELETE 3

The statement returns 3 to indicate that 3 rows have been deleted.

Use PostgreSQL DELETE to delete and return deleted rows

To delete films whose film_id is 10 or 11 from the film_copy table and return the title of the deleted film, use the following statement:

DELETE FROM film_copy
WHERE film_id in (10, 11)
RETURNING film_id, title;

 film_id |      title
---------+------------------
      10 | ALADDIN CALENDAR
      11 | ALAMO VIDEOTAPE
(2 rows)

Here, 2 rows are deleted, and some columns in the deleted 2 rows are returned.

Delete all rows from a table using PostgreSQL DELETE

To delete all films from the film_copy table, use the following statement:

DELETE FROM film_copy;

Conclusion

PostgreSQL DELETE statement is used to delete one or more rows from a table. If there is no WHERE clause, all rows are deleted. With a RETURNING clause, the DELETE statement returns information about deleted rows, otherwise it returns the number of deleted rows.