docs/en/14-reference/03-taos-sql/04-stable.md
CREATE STABLE [IF NOT EXISTS] stb_name (create_definition [, create_definition] ...) TAGS (create_definition [, create_definition] ...) [table_options]
create_definition:
col_name column_definition
column_definition:
type_name [COMPOSITE KEY] [ENCODE 'encode_type'] [COMPRESS 'compress_type'] [LEVEL 'level_type']
table_options:
table_option ...
table_option: {
COMMENT 'string_value'
| SMA(col_name [, col_name] ...)
| KEEP value
| VIRTUAL {0 | 1}
}
Notes:
A supertable can have a maximum of 4096 columns, including tag columns.
The maximum number of columns in a virtual supertable is 32767.
A supertable must have at least three columns: one timestamp column (the primary key column), one metric column, and one tag column.
COMPOSITE KEY: You can specify a second column for the primary key by using the COMPOSITE KEY keyword. The second primary key column must be of integer or VARCHAR type. This column, together with the timestamp column, forms a composite key.
If a supertable has a composite key, two records in the supertable are considered duplicates only when both the timestamp column and the second primary key column are identical. In such cases, the database keeps only the most recent record; otherwise, both records are retained.
TAGS: The TAGS clause defines the tag columns of a supertable. The following restrictions apply:
TIMESTAMP, you must provide a literal timestamp value when inserting data. Arithmetic expressions such as NOW + 10s are not supported.ENCODE and COMPRESS: See Data Compression.
COMMENT and SMA: See Tables.
KEEP: See Databases for details. However, note the following differences between supertable-level KEEP and database-level KEEP:
KEEP value must be smaller than the database-level KEEP value.KEEP does not take effect immediately. You must flush and then compact the database before data marked as expired by the supertable-level KEEP is removed.KEEP value, then compact the database again, expired data might not be completely removed.VIRTUAL: Specify 1 to create a virtual supertable. The following restrictions apply:
COMPOSITE KEY is not supported.ENCODE and COMPRESS) are not supported.The following statement displays information about all supertables in the current database:
SHOW STABLES [LIKE tb_name_wildcard];
The following statement displays the SQL statement that was used to create the specified supertable.
SHOW CREATE STABLE stb_name;
This can be helpful when migrating or cloning existing supertables.
The following statement displays the schema of the specified supertable:
DESCRIBE [db_name.]stb_name;
The following statement displays the tag values of all subtables within a specified supertable:
SHOW TABLE TAGS FROM stb_name [FROM db_name];
or
SHOW TABLE TAGS FROM [db_name.]stb_name;
The subtable name and the value of each tag are shown as follows:
taos> SHOW TABLE TAGS FROM st1;
tbname | id | loc |
======================================================================
st1s1 | 1 | losangeles |
st1s2 | 2 | sanfrancisco |
st1s3 | 3 | sacramento |
Query OK, 3 rows in database (0.004455s)
You can display the values of only specified tags with the following statement:
taos> SELECT DISTINCT TBNAME, id FROM st1;
tbname | id |
===============================================
st1s1 | 1 |
st1s2 | 2 |
st1s3 | 3 |
Query OK, 3 rows in database (0.002891s)
Note that you must include DISTINCT and TBNAME in this statement to ensure accurate and fast results in the event that data is missing or the dataset is large.
The following statement displays the tag values of a specified subtable:
taos> SHOW TAGS FROM st1s1;
table_name | db_name | stable_name | tag_name | tag_type | tag_value |
============================================================================================================
st1s1 | test | st1 | id | INT | 1 |
st1s1 | test | st1 | loc | VARCHAR(20) | sanfrancisco |
Query OK, 2 rows in database (0.003684s)
You can display the values of only specified tags with the following statement:
taos> SELECT DISTINCT TBNAME, id, loc FROM st1s1;
tbname | id | loc |
==================================================
st1s1 | 1 | sanfrancisco |
Query OK, 1 rows in database (0.001884s)
The following statement deletes the specified supertable:
DROP STABLE [IF EXISTS] [db_name.]stb_name;
:::important
When you delete a supertable, all subtables created in the supertable along with their data are also deleted. Use this statement with caution.
:::
Note that deleting a supertable does not immediately free all disk space used by the supertable. The supertable is immediately marked for deletion and queries no longer return results from the supertable, but disk space is not freed until the operating system automatically does so or you manually compact the database.
The following statement modifies the parameters of an existing supertable:
ALTER STABLE [db_name.]tb_name alter_table_clause
alter_table_clause: {
alter_table_options
| ADD COLUMN col_name column_type
| DROP COLUMN col_name
| MODIFY COLUMN col_name column_type
| ADD TAG tag_name tag_type
| DROP TAG tag_name
| MODIFY TAG tag_name tag_type
| RENAME TAG old_tag_name new_tag_name
}
alter_table_options:
alter_table_option ...
alter_table_option: {
COMMENT 'string_value'
| KEEP value
}
Notes:
Modifying the schema of a supertable affects all subtables in the supertable. You cannot alter the schema of any subtable individually. Tag definitions can also only be altered at the supertable level and apply to all subtables in the supertable.
You can perform the following actions:
ADD COLUMN: Add a metric column to the supertable.DROP COLUMN: Delete a metric column from the supertable. Note that this action deletes the metric column from all subtables within the supertable.MODIFY COLUMN: Extend the length of a metric column of type NCHAR or BINARY.
ADD TAG: Add a tag column to the supertable.DROP TAG: Delete a tag column from the supertable. Note that this action deletes the tag column from all subtables within the supertable.MODIFY TAG: Extend the length of a tag column of type NCHAR or BINARY.
RENAME TAG: Change the name of a tag column in the supertable. Note that this action affects all subtables within the supertable.:::important
You cannot add, delete, or modify the primary key column of a supertable.
:::
The following statement adds a metric column to the specified supertable:
ALTER STABLE stb_name ADD COLUMN col_name column_type;
The following statement deletes a metric column from the specified supertable:
ALTER STABLE stb_name DROP COLUMN col_name;
The following statement modifies the length of a specified metric column in the supertable:
ALTER STABLE stb_name MODIFY COLUMN col_name data_type(length);
NCHAR or BINARY.The following statement adds a tag column to the specified supertable:
ALTER STABLE stb_name ADD TAG tag_name tag_type;
Note that the total number of tag columns cannot exceed 128 and the total length cannot exceed 16 KB.
The following statement deletes a tag column from the specified supertable:
ALTER STABLE stb_name DROP TAG tag_name;
The following statement changes the name of a specified tag column in the supertable:
ALTER STABLE stb_name RENAME TAG old_tag_name new_tag_name;
The following statement modifies the length of a specified tag column in the supertable:
ALTER STABLE stb_name MODIFY TAG tag_name data_type(length);
NCHAR or BINARY.You can perform projection and aggregation queries on a supertable using the SELECT statement. You can filter on metrics and tags in the WHERE clause.
If a supertable query does not include an ORDER BY clause, the results returned are grouped by subtable. All records from one subtable are returned first, followed by all records from the next subtable. This means that the data returned is not ordered.
You can include an ORDER BY to ensure that the result set strictly follows the specified ordering.
:::note
All tag operations, except for updating tag values, can be performed only on the supertable. You cannot add, delete, or modify tag columns on a specific subtable individually.
When you add a tag column to a supertable, all subtables in that supertable automatically inherit the new tag, and the default value of the new tag is NULL.
:::