DELETE Command in TimescaleDB

Introduction

In this article we will explain how to use the DELETE command in TimescaleDB. We’ll explain the syntax for the command and we’ll also give a simple demo so you can see how it works. We have some recommended prerequisites but you’re welcome to jump in wherever you feel comfortable.

Prerequisite

Ensure to install and configure the following for your OS:

• PostgreSQL 11

Basic PostgreSQL is required, to be able to follow along with the tutorial.

• TimescaleDB

What is DELETE in TimescaledDB

DELETE command in TimescaleDB allows us to delete rows from a hypertable that propagates down to the associated chunks that builds the hypertable.

DELETE Command In TimescaleDB

The DELETE command in TimescaleDB is the same process as Postgres via standard DELETE structured query language.

Below is the basic syntax for the DELETE command.

Let us discuss the above syntax.

• First, we call the DELETE FROM clause followed by the name of the target table where we want the delete command to take effect.

• Then we use the WHERE clause to handle the conditions we want to satisfy before performing the delete operation.

• It is worthy to note that removing the WHERE clause will delete all the rows in the target table. Don’t do this unless you really want to delete all the data in the table.

DELETE Command in TimescaleDB Example

In this section we will be showing you how to perform the DELETE command in TimescaleDB.

Before we dive into the examples, let’s consider the following table.

DELETE Command with WHERE clause example

The following statement will delete rows with a value in the date_taken equal to 2017-01-01

When we execute the above query it returns the following text DELETE 1, otherwise the text will be DELETE 0 if the value provided to the field in the WHERE clause does not exist.

DELETE Command to delete all rows Example

As mentioned earlier omitting the clause WHERE in a DELETE statement will result to the deletion of the entire rows within the target table.

Our table should be empty, but since we used the clause RETURNING then the database will return all the deleted rows as follows.

NOTE: After performing a large delete operation it is recommended to reclaim the previously occupied storage in a hypertable via VACUUM or VACUUM FULL command, in our case the syntax should be something like this VACUUM(VERBOSE, ANALYZE) dailytemp;

Conclusion

In this article we demonstrated how to use the DELETE command in TimescaleDB. We hope you were able to follow along and come back for more of our tutorials on TimescaleDB.