Time-Series Table Management

A time-series table is a carrier of time-series data, which has the same meaning as a relational table in the relational model. When you use it, you create the database first, then create the time-series table, then you can write or query the time-series data.

This chapter mainly introduces the management of time-series table, including the creation, modification and deletion of time-series table, and the view of time-series table under current database.

Create a Time-Series Table

Used to create a new time-series table under the current database.

Run the following command to create a time-series table.

CREATE TIMESERIES TABLE [ IF NOT EXISTS ] table_name
( column_name data_type [ tslabel ] [ DEFAULT default_expr ] [, ...] [ COLLATE collation ] )
[ POLICY policy_name ]
[ TABLESPACE tablespace_name ];

For more details, see CREATE TIMESERIES TABLE.

Modify a Time-Series Table

Used to modify an existing time-series table. You can modify the column names, column defaults, data retention policy, owner, and tablespace of the time-series table.

Run the following command to modify a time-series table.

ALTER TIMESERIES TABLE [ IF EXISTS ] tablename_name actions

For more details, see ALTER TIMESERIES TABLE.

Delete a Time-Series Table

Used to delete one or more existing time-series tables.

Run the following command to delete a time-series table.

DROP TIMESERIES TABLE [ IF EXISTS ] table_name [, table1_name ...];

For more details, see DROP TIMESERIES TABLE.

Time-Series Tables Views

For user convenience, the system defines a set of time-series table related views.

timeseries_views.tstable, used to display all the time-series tables under the current database.

For more details, see timeseries_views.tstable.

Example:

--Query all time-series tables under the timeseries_views schema
Uqbar=# SELECT * FROM timeseries_views.tstable;
 schemaname | tablename | timecolname |     tagcol      | tspolicy 
------------+-----------+-------------+-----------------+----------
 public     | weather   | time        | {city,location} | infinity
(1 rows)

timeseries_views.tschunkgroup, used to display the chunkgroup of all time-series tables under the current database.

For more details, see timeseries_views.tschunkgroup.

Example:

--Query the ChunkGroup view of the weather time-series table
Uqbar=# SELECT tablename, chunkgroupname, chunkgroupduration, start_time, end_time FROM timeseries_views.tschunkgroup WHERE tablename= 'weather';
 tablename |  chunkgroupname |  chunkgroupduration  |       start_time      |       end_time    
--------------------+--------------------+----------------------+----------------------+----------------
 weather   |      p_1_1      |        7 days        |   2022-6-16 00:00:00  |   2022-6-23 00:00:00
 weather   |      p_1_2      |        7 days        |   2022-6-16 00:00:00  |   2022-6-23 00:00:00
 weather   |      p_2_1      |        7 days        |   2022-6-23 00:00:00  |   2022-6-30 00:00:00
 weather   |      p_2_2      |        7 days        |   2022-6-23 00:00:00  |   2022-6-30 00:00:00

(4 rows)