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:

• PostgreSQL 11

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

• TimescaleDB

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.

• 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.

We can very the result via SELECT statement, and the result should look something like the following.

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.

• 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.

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:

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.