SQL Delete Row

Introduction

In this article we will be showing you how to delete a row in PostgreSQL. This is an extremely common operation you’ll need to perform on your database and we’ll show a couple different variations so you get a feel for how your specific SQL delete row task might be accomplished.

Prerequisites

You should have PostgreSQL installed and running on your system.

The DELETE statement

To delete a row from your SQL database you’ll utilize the DELETE statement which typically looks something like:

For example:

You must give it the name of the table where the row you want to delete is. The table name is a mandatory argument. You should also give it a condition which specifies exactly which rows you want to delete. This condition is optional although if this argument is left out it will delete every row in the table which is typically not what is intended.

The statement itself returns the number of rows that were deleted from the table. It does not always match the number of rows that match the delete condition because sometimes there are triggers that perform some action before deletion BEFORE DELETE which effect the number of rows are actually deleted.

Demos

Imagine this is your table users:

To delete the row with id = 2 you would execute this query:

To delete all the rows in the table you could execute this query:

We’ll show the result so you can see the return value of the statement indicates the number of deleted items:

You could use a more complex query to delete multiple rows. For instance if we wanted to delete all the rows with an id greater than 1:

Verify before Deletion

If this is the first time you write the deletion statement we suggest you execute a select statement first to make sure your condition is right before you actually delete the row. Once you verify the select condition is retrieving the correct rows, you can apply those same conditions in your DELETE statement.

The basics of this query is very simple. The thing that can make this query complex is the delete condition. In our example we deleted rows using their id numbers but in a real world application finding the row you want to delete in a sea of tables and records can be quite difficult. Often you’ll need to join many tables to create the delete condition.

Conclusion

Thank you for joining us in this tutorial of how to delete rows in PostgreSQL. We demonstrated the DELETE command with a few simple examples that you should be able to extend to fit your specific scenario. Please don’t hesitate to reach out to us at Object Rocket if you need help managing your PostgreSQL database. We also work with MongoDB, CockroachDB, Elasticsearch, and Redis. Thanks!

Just the Code

Here’s a recap of the DELETE demo examples: