PostgreSQL - DELETE FROM Table
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.
DELETE Syntax
We use the DELETE FROM table_name to delete rows from tables based on filters mentioned in the WHERE clause.
DELETE FROM table_name WHERE condition;
Delete single row using primary key
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)
Delete multiple rows using WHERE clause
In the following example we will delete all the comments by the employeeId 'e03'.
DELETE FROM comments WHERE "employeeId" = 'e03';
After DELETE:
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)
Delete without WHERE clause
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;
After DELETE:
SELECT * FROM comments;
commentId | commentBody | lastModifiedAt | createdAt | employeeId
-----------+-------------+----------------+-----------+------------
(0 rows)
ADVERTISEMENT