Update Command in TimescaleDB

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

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:

  1. 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;,
  2. 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

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.