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:
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