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 TSTIME and TSTAG columns 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 column whose values range from 1 to 32 is supported to be specified as TSTAG. Support for integer types and string types, integer types include int2, int4, int8; string types incluede “char”, char(n), character(n), nchar(n), bpchar(n), varchar(n), character varying(n), nvarchar(n), varchar2(n), nvarchar2(n) , and text.

• 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

Related Pages

ALTER TIMESERIES TABLE, DROP TIMESERIES TABLE