Skip to main content

Delete statement

In this tutorial, we will delve into the DELETE statement in PostgreSQL. This statement is a key component of SQL and understanding it will help you greatly in managing your databases. The DELETE statement is used to remove rows from a table in a PostgreSQL database.

Introduction to the DELETE Statement​

The DELETE statement is used to delete existing records in a table. Here is the basic syntax of the DELETE statement:

DELETE FROM table_name WHERE condition;
  • table_name is the name of the table from where you want to delete data.
  • WHERE is a clause used to specify the condition that determines which rows to delete. If you omit the WHERE clause, all rows in the table will be deleted!

Deleting Data from a Table​

Let's look at an example where we delete a row from a table named students:

DELETE FROM students WHERE student_id = 3;

In the above statement, we are deleting the student with an id of 3. Note that this will only delete the row where the student_id is 3. All other rows in the table remain unaffected.

Deleting All Data from a Table​

If you want to delete all rows from a table without deleting the table itself, you can use the DELETE statement without the WHERE clause. For example:

DELETE FROM students;

This will delete all rows from the students table. Be careful with this statement as it will remove all your data and cannot be undone.

Returning Deleted Data​

PostgreSQL provides the RETURNING clause that is used to return the deleted rows. Here is the basic syntax:

DELETE FROM table_name WHERE condition RETURNING *;

For example, if you want to delete students who have graduated and return their details, you can use the following statement:

DELETE FROM students WHERE status = 'Graduated' RETURNING *;

This statement will delete all students with a status of 'Graduated' and return all the details of the deleted rows.

Conclusion​

In this tutorial, we've covered the basics of the DELETE statement in PostgreSQL. It's a powerful tool, but also one that should be used with caution, particularly when omitting the WHERE clause, as it can result in the deletion of more data than expected. Always make sure your conditions are correct before executing a DELETE statement.

In the next tutorial, we will cover the UPDATE statement which is used to modify existing data in a table. Stay tuned!

Remember, the best way to learn is by doing. So, try out these commands and explore different scenarios. Happy Learning!