Real-time systems generate a huge quantity and variety of data rigorously that has to be stored in the database. There is a huge chance that data recorded in the Postgres table in such a case will be redundant or outdated. Such data records must be deleted or discarded from the database to clean up the data. This data can then be utilized to perform effective and efficient data analysis.
This article will demonstrate how to delete rows from the Postgres table. So let’s learn it together.
PostgreSQL DELETE – How to Delete Table Rows?
In Postgres, we can delete the redundant and unnecessary table rows by using the Postgres DELETE statement. The basic syntax to use the DELETE statement looks like this:
DELETE FROM t_name
WHERE cond ;
Syntax Description
- The DELETE statement is used to drop/delete an object from the database.
- We have to write the table name after the DELETE FROM statement.
- The WHERE clause is specified in order to delete that specific row where the condition is satisfied.
Let’s see how we can delete table rows from a table.
Example 1: Deleting a Specific Table Row
Let’s consider the table named “messages”. The table looks like this:
We can see that there are some rows that are redundant and useless, so we need to delete them. Let’s consider deleting the row with msg_id 6 which is repeated many times. The query to delete this row can be written as:
DELETE FROM messages
WHERE msg_id = 6 ;
The row will successfully be deleted upon execution of this query.
Now if we want to see the table we will run/execute the SELECT query. This query can be written as:
We can see that the row with msg_id 6 has been deleted.
We can also delete the multiple rows using the DELETE statement. Let’s see how we can do it.
Example 2: Deleting Multiple Table Rows
We can delete multiple rows from a table at the same time. For this purpose, we use the IN statement. Let’s see what the syntax for this scenario looks like.
DELETE FROM t_name
WHERE c_name IN (row1, row2, ..., rowN);
Syntax Description
- To delete multiple rows at the same time we use the IN statement.
- The IN statement is used with the WHERE clause to check in the list provided after it.
- So what the query will do is, it will look for the list of values passed to the IN statement and if they are there, it will delete them.
Let’s figure out how we can make it work. We will delete some rows from the following “messages” table.
Let’s delete the redundant rows from the above table i.e. we will delete the rows with msg_id 7 and 8. The query for this case can be illustrated as:
DELETE FROM messages
WHERE msg_id IN (7,8)
RETURNING *;
This will delete the rows with msg_id 7 and 8. The RETURNING * clause will return the rows that are deleted. The output of this query will be:
We can observe that the query has given us the deleted rows. Now if we want to see the whole table, we will execute the SELECT query:
We can see that both the rows have been deleted from the original table.
Example 3: Deleting All Table Rows
In PostgreSQL, we may also utilize the DELETE statement to delete all of the table’s rows. The basic function of writing the WHERE statement is to add a condition to specify which rows will be deleted. But if we skip the WHERE statement, all of the table’s rows will get deleted. However, the table will not be deleted, it will still be there but the rows will get deleted from the table.
Let’s try to delete all the rows of the “messages” table.
DELETE FROM messages;
This query will delete the columns successfully.
You can observe that all the table rows got deleted. Now if we want to ensure this we will run the SELECT query like this:
We can see that all the rows are deleted in the table. The table will not be deleted.
So This is how we can delete the table’s rows from a database using the DELETE statement.
Conclusion
The user can delete the table’s rows in PostgreSQL by utilizing the DELETE statement. The DELETE clause deletes the table’s rows that satisfy the condition given after the WHERE clause. To delete the multiple rows we use the IN statement which is followed by the list of values. The row gets deleted if it satisfies the values provided. Moreover, we can skip the WHERE clause to delete all the table’s rows of the selected table.
