Update Command in TimescaleDB
Introduction
In this article we will discuss the update command in TimescaleDB. There are some recommended prerequisites but you’re also welcome to just jump right in. We have other articles about the other CRUD operations in TimescaleDB but this article will be dedicated to the update command, what it does, and some demos with real code examples on how to do it.
Prerequisite
Ensure to install and configure the following for your OS:
Basic PostgreSQL is required, to be able to follow along with the tutorial.
Update Command In TimescaleDB
TimescaledDB fully supports SQL so if you’re familiar with SQL the commands for performing the UPDATE statement should be familiar. We can update data from a hypertable via standard UPDATE statement.
Let us consider the following table that we will be using for this demo.
The above table is the monthly report for a daily record temperature in Delhi.
NOTE:
- We need to extend the database with TimescaleDB by connecting to the target database and using the following command
CREATE EXTENSION IF NOT EXISTS Timescaledb cascade;
, - We convert the table into hypertable via
create_hypertable
command.
Updating Single Record into Hypertable Example
In this section, we will be looking at how to update a single record into a hypertable via the UPDATE command. We will be using the following statement.
1 2 | UPDATE TABLE_NAME SET column1 = value1, column2 = value2, ... WHERE conditions. |
- First, we call the
UPDATE
clause followed by the ‘table_name’. - Second, we specify the column names and the corresponding values after the SET clause.
- Third, specify a condition using the WHERE clause. The WHERE clause will limit the update to a certain row only.
1 2 | UPDATE dailytemp SET humidity = 71 WHERE date_taken = '2017-01-04'; |
We can very the result via SELECT statement, and the result should look something like the following.
1 2 3 4 5 | dailytempdb=# select * from dailytemp where date_taken = '2017-01-04'; date_taken | meantemp | humidity | wind_speed | meanpressure ------------+----------+----------+------------+-------------- 2017-01-04 | 18.7 | 71 | 4.545 | 1015.7 (1 row) |
Updating Multiple Record into Hypertable Example
We performed a single update in the previous section, now in this section, we will be showing you how to update multiple rows or records.
We will be using the previous table for this example.
The basic form of updating multiple records is as follows.
1 2 | UPDATE TABLE_NAME SET column1 = value1, column2 = value2, ... WHERE conditions. |
- We call the UPDATE clause and then we specify the table that we want to update.
- Then, we invoke the SET clause against the columns and the new values to be appended.
- the
WHERE
clause is optional as it controls the number of records to be updated.
UPDATE statement with WHERE clause
Now we can perform the update of multiple record within the hypertable.
1 2 | UPDATE dailytemp SET wind_speed = 3 WHERE meantemp < 15; |
The above query will update multiple tables within the dailytemp where the value of their ‘meantemp’ column is less than fifteen, after executing Postgres notifies us with the following text: ‘UPDATE 12’.
We can verify via SELECT statement, the result should look something like the following:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | dailytempdb=# select * from dailytemp where meantemp < 15; date_taken | meantemp | humidity | wind_speed | meanpressure ------------+------------------+------------------+------------+------------------ 2017-01-07 | 14.7083333333333 | 95.8333333333333 | 3 | 1011.375 2017-01-09 | 14.5714285714286 | 80.8095238095238 | 3 | 1015.95238095238 2017-01-10 | 12.1111111111111 | 71.9444444444444 | 3 | 1016.88888888889 2017-01-11 | 11 | 72.1111111111111 | 3 | 1016.77777777778 2017-01-12 | 11.7894736842105 | 74.5789473684211 | 3 | 1016.36842105263 2017-01-13 | 13.2352941176471 | 67.0588235294118 | 3 | 1017.52941176471 2017-01-14 | 13.2 | 74.28 | 3 | 1018.84 2017-01-16 | 14.65 | 78.45 | 3 | 1017.15 2017-01-17 | 11.7222222222222 | 84.4444444444444 | 3 | 1018.38888888889 2017-01-18 | 13.0416666666667 | 78.3333333333333 | 3 | 1021.95833333333 2017-01-19 | 14.6190476190476 | 75.1428571428571 | 3 | 1022.80952380952 2017-01-28 | 14.8636363636364 | 82.7727272727273 | 3 | 1019.63636363636 (12 rows) |
NOTE: Removing the WHERE
clause in the UPDATE statement will update the whole rows/records of the table.
Conclusion
We hope you found these demonstrations of how to use the update command in TimescaleDB helpful. Thank you for joining us for another TimescaleDB tutorial from Object Rocket.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started