DELETE Command in TimescaleDB

Have a Database Problem? Speak with an Expert for Free
Get Started >>

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:

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

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.

1
2
DELETE FROM TABLE_NAME
WHERE <conditions>

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
 date_taken |     meantemp     |     humidity     |    wind_speed    |   meanpressure
------------+------------------+------------------+------------------+------------------
 2017-01-01 | 15.9130434782609 | 85.8695652173913 | 2.74347826086957 |               59
 2017-01-02 |             18.5 | 77.2222222222222 | 2.89444444444444 | 1018.27777777778
 2017-01-03 | 17.1111111111111 | 81.8888888888889 | 4.01666666666667 | 1018.33333333333
 2017-01-05 | 18.3888888888889 | 74.9444444444444 |              3.3 | 1014.33333333333
 2017-01-06 | 19.3181818181818 | 79.3181818181818 | 8.68181818181818 | 1011.77272727273
 2017-01-08 | 15.6842105263158 | 83.5263157894737 |             1.95 |          1015.55
 2017-01-15 | 16.4347826086957 | 72.5652173913043 |  3.6304347826087 | 1018.13043478261
 2017-01-20 | 15.2631578947368 | 66.4736842105263 | 11.2263157894737 | 1021.78947368421
 2017-01-21 | 15.3913043478261 | 70.8695652173913 |  13.695652173913 | 1020.47826086957
 2017-01-22 |            18.44 |            76.24 |            5.868 |          1021.04
(10 rows)

DELETE Command with WHERE clause example

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

1
2
DELETE FROM dailytemp
WHERE date_taken = '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.

1
2
DELETE FROM dailytemp
RETURNING *;

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

1
2
3
4
5
6
7
8
9
10
11
12
 date_taken |     meantemp     |     humidity     |    wind_speed    |   meanpressure
------------+------------------+------------------+------------------+------------------
 2017-01-02 |             18.5 | 77.2222222222222 | 2.89444444444444 | 1018.27777777778
 2017-01-03 | 17.1111111111111 | 81.8888888888889 | 4.01666666666667 | 1018.33333333333
 2017-01-05 | 18.3888888888889 | 74.9444444444444 |              3.3 | 1014.33333333333
 2017-01-06 | 19.3181818181818 | 79.3181818181818 | 8.68181818181818 | 1011.77272727273
 2017-01-08 | 15.6842105263158 | 83.5263157894737 |             1.95 |          1015.55
 2017-01-15 | 16.4347826086957 | 72.5652173913043 |  3.6304347826087 | 1018.13043478261
 2017-01-20 | 15.2631578947368 | 66.4736842105263 | 11.2263157894737 | 1021.78947368421
 2017-01-21 | 15.3913043478261 | 70.8695652173913 |  13.695652173913 | 1020.47826086957
 2017-01-22 |            18.44 |            76.24 |            5.868 |          1021.04
(9 rows)

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.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.