CREATE TIMESERIES TABLE
Function
CREATE TIMESERIES TABLE creates a time-series table in the current database. The time-series table is owned by the creator.
Precautions
-
The
TSTIMEandTSTAGcolumns must be specified. -
Only one column can be specified as the TSTIME column, and the data type is the time type, including timestamp [without time zone] and timestamp with time zone. The date type in openGauss is actually timestamp [without time zone]. Apart from the date, the time information is also included. TSTIME supports the date type but the date type includes only the date without the time. Therefore, it is not supported.
-
The column whose values range from 1 to 32 is supported to be specified as TSTAG. The data type must be the string, including “char”, char(n), character(n), nchar(n), bpchar(n), varchar(n), character varying(n), nvarchar(n), varchar2(n), nvarchar2(n) , and text. clob and name are not supported.
-
At least one column is not TSTIME and TSTAG.
-
The length of the table name must be lower than or equal to 63 characters.
-
A time-series table can have at most one data retention policy specified.
-
TSTIME does not allow users to set the default value. By default, it is set to the current time.
-
The field allows users to set it to NOT NULL.
-
The field does not support customization of the data type. Due to restrictions to the column-store data type, the supported data types are as follows (the date type in DATE and DATEARRAY means "date" not date):
BOOL, HLL, BYTEA, CHAR, INT8, INT2, INT4, INT1, NUMERIC, BPCHAR, VARCHAR, NVARCHAR2, SMALLDATETIME, TEXT, OID, FLOAT4, FLOAT8, ABSTIME, RELTIME, TINTERVAL, INET, DATE, TIME, TIMESTAMP, TIMESTAMPTZ, INTERVAL, TIMETZ, MONEY, CIDR, BIT, VARBIT, CLOB, BOOLARRAY, HLL_ARRAY, BYTEARRAY, CHARARRAY, INT8ARRAY, INT2ARRAY, INT4ARRAY, INT1ARRAY, NUMERICARRAY, BPCHARARRAY, VARCHARARRAY, NVARCHAR2ARRAY, SMALLDATETIMEARRAY, TEXTARRAY, FLOAT4ARRAY, FLOAT8ARRAY, ABSTIMEARRAY, RELTIMEARRAY, INTERVALARRAY, INETARRAY, DATEARRAY, TIMEARRAY, TIMESTAMPARRAY, TIMESTAMPTZARRAY, ARRAYINTERVAL, TIMETZARRAY, MONEYARRAY, CIDRARRAY, BITARRAY, VARBITARRAY
Syntax
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 ];Parameter Description
-
table_name
Specifies the name of a time-series table.
-
tslabel
Specifies the the data type of a column in a time-series table during time-series database creation. It means that TSTIME or TSTAG column is specified. TSTIME indicates the time column of a time-series table. TSTAG indicates the tag column of a time-series table.
-
default_expr
Specifies the default value of a column.
-
policy_name
Specifies the policy name of a time-series table. The system creates a default policy for each database. Users can also set a policy as the default one. If no policy is specified during time-series table creation, the default retention policy of the database to which the time-series table belongs is used.
-
tablespace_name
Specifies the name of a tablespace to which a time-series table belongs.
Examples
--Create a simple time-series table.
Uqbar=# CREATE TIMESERIES TABLE weather(time timestamp TSTIME, city text TSTAG, location text TSTAG, temperature float) POLICY infinity;
CREATE TIMESERIES TABLE