SQL DELETE Statement using PostgreSQL

Introduction

If you’re getting your feet wet with SQL database technology and learning the basic commands, the DELETE statement will quickly be on your radar. In this article we cover the SQL DELETE statement and will show a few different ways to use the command. We will demo with PostgreSQL a tried and true SQL database technology. You can apply what you learn here to most SQL database technologies but the syntax we use here is specifically aimed at PostgreSQL.

Prerequisite

You should have PostgreSQL installed and running.

The PostgreSQL DELETE Statement

The DELETE statement is pretty self-explanatory in that it allows you to delete rows from a table. The syntax for using it is below:

As you can see when you use the DELETE statement you need to provide it with two arguments:

• The table name that you want to delete records from.
• A condition which let’s you specify which rows you’d like to delete from the table. This condition is supplied after the WHERE statement. Be aware that this clause is optional. If you don’t provide the WHERE clause with a condition all records will be deleted.

What does the statement return when executed? The statement returns the number of rows that are deleted from the table. ( There are exceptions to this on account of triggers ). If no rows are deleted the statement will return 0.

Examples of DELETE Statement in PostgreSQL

To demo some examples let’s first create a table that we can work with. Let’s imagine a small grocery store needs a database and a table for their products. We create the following products table:

Next we add some products to our table so that we can have some data to DELETE:

Example 1 – Delete by Id ( A delete by condition )

Our first example will be to delete a product by id which is a common use case. Let’s query all the products in the table then pick a product by it’s id:

Output:

Now if we wanted to delete the Soy Milk product with an id of 2 we could execute the following query:

Output:

Notice how it returned the number of rows deleted. Let’s verify that the row was deleted:

Output:

The Soy Milk product was deleted.

You can use this type of query for tons of things. We’ll include a few more examples:

• If you wanted to delete products over a certain price:

• If you wanted to delete products over multiple conditions:

Example 3 – Delete all records

The other most common use case is to delete all rows of a table. To do so use the DELETE statement like so:

Output:

Notice that the query has returned 4 which tells us it deleted 4 rows ( the rest of the entries in our table).

Conclusion

We have demonstrated how to use the SQL DELETE statement using PostgreSQL. You can use it to delete a single row or many rows from a table. We hope you can apply the techniques we’ve demonstrated to your own circumstances.

If you weren’t able to solve your issue and would like some suggestions or want to pass off the management of your database so you can focus on other areas of your application, please don’t hesitate to reach out to us at Object Rocket. We’re happy to discuss your application and your needs.