PostgreSQL
In this tutorial we will be learning to delete rows from tables in PostgreSQL.
This tutorial is related to the previous tutorials.
Check this tutorial PostgreSQL - INSERT INTO Table to learn how to insert rows into a table.
Check this tutorial PostgreSQL - SELECT FROM Table to learn how to fetch rows from a table.
We use the DELETE FROM table_name to delete rows from tables based on filters mentioned in the WHERE clause.
DELETE FROM table_name
DELETE FROM table_name WHERE condition;
In the following example we are deleting comment having commentid 10.
Before DELETE:
SELECT * FROM comments; commentId | commentBody | lastModifiedAt | createdAt | employeeId -----------+---------------------+---------------------+---------------------+------------ 1 | Awesome | 2021-01-01 02:03:04 | 2021-01-01 02:03:04 | e03 2 | Hello World | 2018-01-02 01:02:03 | 2018-01-02 01:02:03 | e03 3 | Happy | 2018-01-02 01:04:03 | 2018-01-02 01:04:03 | e01 4 | This is my comment. | 2018-01-01 02:03:04 | 2018-01-01 02:03:04 | e04 5 | Keep coding.... | 2018-04-04 03:02:01 | 2018-05-04 03:02:01 | e01 6 | Hello | 2018-01-01 04:03:04 | 2018-01-01 04:03:04 | e04 7 | ABC... | 2018-01-01 04:03:04 | 2018-01-01 04:03:04 | e01 8 | Andriod | 2018-01-01 04:03:04 | 2018-01-01 04:03:04 | e05 9 | Apple | 2018-01-01 04:03:04 | 2018-01-01 04:03:04 | e03 10 | Super | 2018-01-01 04:03:04 | 2018-01-01 04:03:04 | e01 11 | Awesome | 2018-01-01 04:03:04 | 2018-01-01 04:03:04 | e02 (11 rows)
DELETE Query:
DELETE FROM comments WHERE "commentId" = 10;
After DELETE:
SELECT * FROM comments; commentId | commentBody | lastModifiedAt | createdAt | employeeId -----------+---------------------+---------------------+---------------------+------------ 1 | Awesome | 2021-01-01 02:03:04 | 2021-01-01 02:03:04 | e03 2 | Hello World | 2018-01-02 01:02:03 | 2018-01-02 01:02:03 | e03 3 | Happy | 2018-01-02 01:04:03 | 2018-01-02 01:04:03 | e01 4 | This is my comment. | 2018-01-01 02:03:04 | 2018-01-01 02:03:04 | e04 5 | Keep coding.... | 2018-04-04 03:02:01 | 2018-05-04 03:02:01 | e01 6 | Hello | 2018-01-01 04:03:04 | 2018-01-01 04:03:04 | e04 7 | ABC... | 2018-01-01 04:03:04 | 2018-01-01 04:03:04 | e01 8 | Andriod | 2018-01-01 04:03:04 | 2018-01-01 04:03:04 | e05 9 | Apple | 2018-01-01 04:03:04 | 2018-01-01 04:03:04 | e03 11 | Awesome | 2018-01-01 04:03:04 | 2018-01-01 04:03:04 | e02 (10 rows)
In the following example we will delete all the comments by the employeeId 'e03'.
DELETE FROM comments WHERE "employeeId" = 'e03';
SELECT * FROM comments; commentId | commentBody | lastModifiedAt | createdAt | employeeId -----------+---------------------+---------------------+---------------------+------------ 3 | Happy | 2018-01-02 01:04:03 | 2018-01-02 01:04:03 | e01 4 | This is my comment. | 2018-01-01 02:03:04 | 2018-01-01 02:03:04 | e04 5 | Keep coding.... | 2018-04-04 03:02:01 | 2018-05-04 03:02:01 | e01 6 | Hello | 2018-01-01 04:03:04 | 2018-01-01 04:03:04 | e04 7 | ABC... | 2018-01-01 04:03:04 | 2018-01-01 04:03:04 | e01 8 | Andriod | 2018-01-01 04:03:04 | 2018-01-01 04:03:04 | e05 11 | Awesome | 2018-01-01 04:03:04 | 2018-01-01 04:03:04 | e02 (7 rows)
In this example we are going to run the DELETE query without the WHERE clause. This is dangerous as it will delete all the rows from the table.
Do not run DELETE query without the WHERE clause.
DELETE FROM comments;
SELECT * FROM comments; commentId | commentBody | lastModifiedAt | createdAt | employeeId -----------+-------------+----------------+-----------+------------ (0 rows)