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

ADVERTISEMENT

ADVERTISEMENT

ADVERTISEMENT

ADVERTISEMENT

ADVERTISEMENT