How to Delete Row in PostgreSQL

In this PostgreSQL tutorial, I will teach you how to delete row in PostgreSQL. Also, you will learn about deleting a single row or multiple rows from the table.

Then at the end, I will show you how to return the deleted rows from the table and also how to delete all the rows from the table.

How to Delete Row in PostgreSQL

PostgreSQL provides the keyword DELETE FROM to remove the rows from the table. Sometimes, in your database some information becomes irrelevant or you don’t need that information anymore. So you should remove that kind of information from the database.

Suppose you performing some data analysis on the database for decision-making, if your database contains unnecessary information, then your data analysis will yield the wrong result and on the basis of that result, your decision-making can also be wrong.

So always delete the unwanted data from the database, for good decision-making.

The syntax for deleting rows from the table is given below.

DELETE FROM tableName
WHERE condition;

• DELETE FROM: It is the keyword to delete the rows from the specified table.
• tableName: The name of the table in which you want to delete the rows.
• WHERE condition: To remove specific rows from the table based on the condition. If you forget to mention the WHERE condition, then DELETE FROM will remove all the rows from the table.

Let’s see some examples and understand how to remove the rows from the table.

How to Delete Single Row in PostgreSQL

To delete a single row from the table, you need to specify that single row within the condition of the WHERE clause.

Suppose you have table ‘customers_data’ with columns customer_id, name, and country, the table is shown below.

Now you have a situation where you need to remove a customer Emma White who lives in the USA with a customer ID equal to 13, look at the above picture.

To delete the customer with an ID equal to 13 use the below command.

DELETE FROM customers_data
WHERE customer_id = 13;

SELECT customer_id, name, country FROM customers_data;

After executing the above query, the customer Emma White with ID equal to 13 is removed from the table as you can see in the output within column ‘customer_id’. This is how you can delete a single row from the table.

How to Delete Multiple Rows in PostgreSQL

You can also delete more than one row from the table, for that, you will use the IN operator within the WHERE clause to specify the multiple row.

Let’s say you want to delete the customer_id with 15 and 16, then use the below query.

DELETE FROM customers_data
WHERE customer_id IN(15,16);

SELECT customer_id, name, country FROM customers_data;

The above query removes the customers with IDs equal to 15 and 16 that you can see in the output. To remove the multiple rows, the IN operator is used in the WHERE clause as customer_id IN(15,16). Within IN you can provide any number of IDs to which you want to delete.

How to Get Deleted Row in PostgreSQL

In the previous examples, you deleted the rows from the table and then you use the SELECT statement to view whether rows are deleted from the table or not.

Here you will see how to get the deleted rows from the table as soon as you execute the DELETE FROM query to remove the rows from the table.

The syntax is given below.

DELETE FROM tableName
WHERE condition
RETURNING *;

• DELETE FROM: It is the keyword to delete the rows from the specified table.
• tableName: The name of the table in which you want to delete the rows.
• WHERE condition: To remove specific rows from the table based on the condition. If you forget to mention the WHERE condition, then DELETE FROM will remove all the rows from the table.
• RETURNING *: Use it this clause to return the deleted rows from the table.

Let’s take an example where you want to delete the customer ID equal to 14 in the table customers_data, so use the below query.

DELETE FROM customers_data
WHERE customer_id = 14
RETURNING *;

The above query removes the customer with an ID equal to 14 and then returns the deleted row as the result that you can see in the above output.

You get the info about deleted rows in the output because of the RETURNING * clause that you have used after the WHERE clause condition in the query.

You can also use the RETURNING * clause when you delete multiple rows from a table and it will show all the deleted rows in the output.

How to Delete All Row in PostgreSQL

The DELETE FROM keyword deletes all the rows from the table if you don’t provide any condition using the WHERE clause.

The syntax is given below to delete all the rows from the table.

DELETE FROM table_name;

• DELETE FROM: It is the keyword to remove the rows from the specified table.
• table_name: The name of the table in which you want to delete all the rows.

Now you use the below command to delete all the customer data from the table ‘customers_data’.

DELETE FROM customers_data;

SELECT customer_id, name, country FROM customers_data;

After running the above query, it removes or deletes all the customer records from the table that you can see in the above output. This is how you can delete all the records from the table using the DELETE FROM keyword on a specific table.

Conclusion

In this PostgreSQL tutorial, you learned how to delete one or multiple rows from the table with different examples. Additionally, you learned how to return the deleted rows as output using the RETURNING clause.

You may also read:

• How to Drop All Tables in PostgreSQL
• PostgreSQL WHERE Clause
• How to Update Query In PostgreSQL