If you’re using TimescaleDB to store data that changes with time, it can be helpful to understand how to work with tables in this time-series database. TimescaleDB was created as an extension to PostgreSQL, which means that it supports all the operations, queries and objects you may already be familiar with in PostgreSQL. However, a key difference exists: TimescaleDB utilizes a unique abstracted structure called a hypertable. Hypertables provide users with the all the benefits of automatic partitioning without the complexity. In this article, we’ll discuss the TimescaleDB hypertable and the various operations you can perform on one, and we’ll look at some examples to see how it all works.
Before you attempt the examples in this tutorial, make sure to install and configure the following:
Basic knowledge of PostgreSQL is also required to follow along with this tutorial.
What Is a Hypertable?
When users work with “tables” in TimescaleDB, what they’re really interacting with is an abstracted structure called a hypertable. TimescaleDB implements automatic partitioning that divides data into “chunks”; a hypertable is a virtual view of multiple chunks that behaves like a single table. This means that if you’re familiar with PostgreSQL and know some basic SQL commands, you’ll have no trouble interacting with TimescaleDB. Common PostgreSQL commands such as ALTER, CREATE and DELETE TABLE are identical in TimescaleDB, even though they’re performed on hypertables instead of traditional tables. When a command is made against a TimescaleDB hypertable, the changes are propagated to all of its underlying chunks.
How to Create a Hypertable
Now that we’ve explained what a hypertable is, let’s discuss how to create a TimescaleDB hypertable.
We can create a hypertable in two steps:
- First, we can create a table using a standard CREATE TABLE statement. The statement would look like the following:
CREATE TABLE TABLE_NAME(
column_name <data_type> <column_constraints>
Let’s discuss this syntax in a bit more detail:
- We use the
CREATE TABLEclause, then we specify the name of the table.
- Next, we specify the column name, the data type we want to assign and the column constraint. We can specify multiple columns by separating them with commas. The column constraints define any rules we want to enforce on the data we will store in the given column.
Here’s an example of a
CREATE TABLE statement:
CREATE TABLE sample_table(
id INTEGER PRIMARY KEY,
TIME TIMESTAMPTZ NOT NULL,
name VARCHAR NOT NULL
This statement creates a table named ‘sample_table’ with three columns. These columns have different data types and column constraints.
- Our second step is to execute the command
create_hypertable()against this newly-created table. The syntax is as follows:
SELECT create_hypertable(<table_name>, <partitioning_option>);
This syntax converts the table to a hypertable and provides a time partitioning option.
Let’s look at an example of this command in action:
The above statement converts the ‘sample_table’ table into a hypertable with time partitioning against the ‘time’ column.
How to Alter a Hypertable
In the previous section, we showed you how to create a hypertable. Now, let’s look at how to alter an existing hypertable.
To do this, we use the following statement:
ALTER TABLE sample_table action;
What exactly is happening in this statement? Let’s take a look:
- First, we call the ALTER TABLE clause, followed by the name of the table.
- We then specify what action should be taken. Some examples of actions include: dropping a column, renaming a column, changing a column’s data type, adding a column, setting a default value to a column, checking the column constraints and renaming the table.
Here’s an example of an
ALTER TABLE statement:
ALTER TABLE sample_table
ADD COLUMN date_published DATE NOT NULL;
The above statement will alter the
sample_table table by adding a new column named ‘date_published’ with a DATE data type. This column has a NOT NULL constraint, which means that values in this column cannot be null.
How to Delete a Hypertable
In TimescaleDB, deleting a hypertable can be accomplished using the standard DROP TABLE command. Performing this command will delete all underlying chunks within that hypertable. We use the following syntax:
DROP TABLE <IF EXISTS> TABLE_NAME`
The above statement will delete the table only if it exists– this means the DROP TABLE command will not return an error if the table we are trying to delete does not exist.
Here’s an example:
DROP TABLE IF EXISTS sample_table;
The above statement deletes
sample_table if it exists; otherwise, it does nothing.
If you’re getting started in TimescaleDB and you have some experience with SQL, you’re in luck: This time-series database supports SQL operations and objects supported by PostgreSQL. Although TimescaleDB uses an abstract structure called a hypertable to enable automatic partitioning, it’s important to know that hypertables behave just like standard PostgreSQL tables, and they can be created, altered and dropped with the same commands. In this article, we provided an introduction to the TimescaleDB hypertable and explained how to use commands such as CREATE TABLE, ALTER TABLE and DELETE TABLE to manage hypertables. With our instructions to help you, you’ll be prepared to manage hypertables in your own TimescaleDB environment.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started