Summary: in this tutorial, you will learn how to use PostgreSQL DELETE statement to delete rows of a table.
Introduction to PostgreSQL DELETE statement
To delete rows in a table, you use PostgreSQL DELETE statement as follows:
1 2 | DELETE FROM table WHERE condition |
First, specify the table where you want to delete in the DELETE FROM clause.
Second, specify which row to delete by using the condition in the WHERE clause. If you omit the WHERE clause, all rows in the table are deleted.
The DELETE statement returns the number of rows deleted. It may not the same as the number of rows specified by the condition in the WHERE clause because the table may contain a BEFORE DELETE trigger that does something else before deletion. If no row deleted, the DELETE statement returns zero.
In case you want to check condition that relates to other columns in another table, you need to use USING clause as the following statement:
1 2 3 | DELETE FROM table USING another_table WHERE table.id = another_table.id AND … |
If you do not want to use the USING clause, you can use sub-select as follows:
1 2 | DELETE FROM table WHERE table.id = (SELECT id FROM another_table); |
PostgreSQL DELETE examples
We will use the linkand link_tmptables that we created in the inserting data tutorial. The following illustrates the content of the linktable:
1 2 3 4 | SELECT * FROM link; |
If you want to load those tables for practicing, you can download them via the following link:
PostgreSQL DELETE with WHERE clause example
To delete the row with id 8, you use the following statement:
1 2 | DELETE FROM link WHERE ID = 8; |
PostgreSQL DELETE USING statement example
If you want to remove all rows in the linktable that values of the id columns are in the link_tmptable.
First, check the link_tmp table data:
1 2 3 4 | SELECT * FROM link_tmp; |
We expect that the rows with id 6 and 7 will be removed from the linktable:
Second, use the following DELETE statement with the USING clause to delete the rows in the linktable:
1 2 3 4 5 | DELETE FROM link USING link_tmp WHERE link.id = link_tmp.id; |
Third, query the linktable to verify the delete operation:
PostgreSQL delete all rows in a table
To remove all rows in the link table, you omit the WHEREclause in the DELETEstatement:
1 2 3 | DELETE FROM link; |
The linktable now is empty.
To delete all rows in the link_tmptable and return the deleted rows, you use the RETURNINGclause in the DELETEstatement as follows:
1 2 3 | DELETE FROM link RETURNING *; |
In this tutorial, we have shown you how to use the PostgreSQL DELETEstatement to delete data in a table.