Introduction to PostgreSQL DELETE statement

The PostgreSQLDELETE statement allows you to delete one or more rows from a table.

The following shows the basic syntax of theDELETE statement:

DELETE FROM table_nameWHERE condition;

In this syntax:

• First, specify the name (table_name) of the table from which you want to delete data after theDELETE FROM keywords.
• Second, specify a condition in theWHERE clause to determine which rows to delete.

TheWHERE clause is optional. If you omit theWHERE clause, theDELETE statement will delete all rows in the table.

TheDELETE statement returns the number of rows deleted. It returns zero if theDELETE statement did not delete any row.

To return the deleted row(s) to the client, you use theRETURNING clause as follows:

DELETE FROM table_nameWHERE conditionRETURNING (select_list |*)

The asterisk (*) allows you to return all columns of the deleted row(s). If you want to return the values in a specific column, you can also specify them after theRETURNING keyword.

For example, the following statement deletes rows from a table and returns the values of the id of the deleted rows:

DELETE FROM table_nameWHERE conditionRETURNING id;

If you delete data from a table based on data from another table, you can use theDELETE JOIN statement.

To delete data that have aforeign key relationship, you use theON DELETE CASCADE option.

Note that theDELETE statement removes data from a table but doesn’t modify the structure of the table. If you want to change the structure of a table such as removing a column, you should use theALTER TABLE statement instead.

PostgreSQL DELETE statement examples

Let’s explore some examples of using theDELETE statement.

Setting up a sample table

The following statementscreate a new table calledtodos andinsert some sample data:

CREATE TABLE todos (    idSERIAL PRIMARY KEY,    titleVARCHAR(255)NOT NULL,    completedBOOLEAN NOT NULL DEFAULT false);INSERT INTO todos (title, completed)VALUES    ('Learn basic SQL syntax', true),    ('Practice writing SELECT queries', false),    ('Study PostgreSQL data types', true),    ('Create and modify tables', false),    ('Explore advanced SQL concepts', true),    ('Understand indexes and optimization', false),    ('Backup and restore databases', true),    ('Implement transactions', false),    ('Master PostgreSQL security features', true),    ('Build a sample application with PostgreSQL', false);SELECT * FROM todos;

Output:

id |                   title                    | completed----+--------------------------------------------+-----------  1 | Learn basic SQL syntax                     | t  2 | Practice writing SELECT queries            | f  3 | Study PostgreSQL data types                | t  4 | Create and modify tables                   | f  5 | Explore advanced SQL concepts              | t  6 | Understand indexes and optimization        | f  7 | Backup and restore databases               | t  8 | Implement transactions                     | f  9 | Master PostgreSQL security features        | t 10 | Build a sample application with PostgreSQL | f(10 rows)

1) Using PostgreSQL DELETE to delete one row from the table

The following statement uses theDELETE statement to delete one row with the id 1 from thetodos table:

DELETE FROM todosWHERE id= 1;

The statement returns 1 indicating that one row has been deleted:

DELETE 1

The following statement uses theDELETE statement to delete the row with id 100:

DELETE FROM todosWHERE id = 100;

Since the row with the id 100 does not exist, theDELETE statement returns 0:

DELETE 0

2) Using PostgreSQL DELETE to delete a row and return the deleted row

The following statement uses theDELETE statement to delete the row with id 2 and return the deleted row to the client:

DELETE FROM todosWHERE id = 2RETURNING *;

PostgreSQL returns the following deleted row:

id |              title              | completed----+---------------------------------+-----------  2 | Practice writing SELECT queries | f(1 row)

3) Using PostgreSQL DELETE to delete multiple rows from the table

The following statement uses theDELETE statement to delete all rows from thetodos table with the value in the value in the completed columntrue and return deleted rows:

DELETE FROM todosWHERE completed = trueRETURNING *;

Output:

id |                title                | completed----+-------------------------------------+-----------  3 | Study PostgreSQL data types         | t  5 | Explore advanced SQL concepts       | t  7 | Backup and restore databases        | t  9 | Master PostgreSQL security features | t(4 rows)DELETE 4

It deleted four rows from thetodos table.

4) Using PostgreSQL DELETE to delete all rows from the table

The following statement uses theDELETE statement without aWHERE clause to delete all rows from thetodos table:

DELETE FROM todos;

Output:

DELETE 4

Thetodos table now is empty.

Summary

• Use theDELETE FROM statement to delete one or more rows from a table.
• Use theWHERE clause to specify which rows to be deleted.
• Use theRETURNING clause to return the deleted rows.

Last updated onJanuary 23, 2024

On this page

• Introduction to PostgreSQL DELETE statement
• PostgreSQL DELETE statement examples
• Summary