COLUMNS
The COLUMNS provides detailed information about columns in tables.
USE INFORMATION_SCHEMA;
DESC COLUMNS;
The output is as follows:
+--------------------------+--------+------+------+---------+---------------+
| Column | Type | Key | Null | Default | Semantic Type |
+--------------------------+--------+------+------+---------+---------------+
| table_catalog | String | | NO | | FIELD |
| table_schema | String | | NO | | FIELD |
| table_name | String | | NO | | FIELD |
| column_name | String | | NO | | FIELD |
| ordinal_position | Int64 | | NO | | FIELD |
| character_maximum_length | Int64 | | YES | | FIELD |
| character_octet_length | Int64 | | YES | | FIELD |
| numeric_precision | Int64 | | YES | | FIELD |
| numeric_scale | Int64 | | YES | | FIELD |
| datetime_precision | Int64 | | YES | | FIELD |
| character_set_name | String | | YES | | FIELD |
| collation_name | String | | YES | | FIELD |
| column_key | String | | NO | | FIELD |
| extra | String | | NO | | FIELD |
| privileges | String | | NO | | FIELD |
| generation_expression | String | | NO | | FIELD |
| greptime_data_type | String | | NO | | FIELD |
| data_type | String | | NO | | FIELD |
| semantic_type | String | | NO | | FIELD |
| column_default | String | | YES | | FIELD |
| is_nullable | String | | NO | | FIELD |
| column_type | String | | NO | | FIELD |
| column_comment | String | | YES | | FIELD |
| srs_id | Int64 | | YES | | FIELD |
+--------------------------+--------+------+------+---------+---------------+
24 rows in set (0.00 sec)
Create a table public.t1 and query the information in the COLUMNS table:
CREATE TABLE public.t1 (h STRING, v FLOAT64, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP() TIME INDEX, PRIMARY KEY(h));
SELECT * FROM COLUMNS WHERE table_schema='public' AND TABLE_NAME='t1'\G
The output is as follows:
*************************** 1. row ***************************
table_catalog: greptime
table_schema: public
table_name: t1
column_name: h
ordinal_position: 1
character_maximum_length: 2147483647
character_octet_length: 2147483647
numeric_precision: NULL
numeric_scale: NULL
datetime_precision: NULL
character_set_name: utf8
collation_name: utf8_bin
column_key: PRI
extra:
privileges: select,insert
generation_expression:
greptime_data_type: String
data_type: string
semantic_type: TAG
column_default: NULL
is_nullable: Yes
column_type: string
column_comment: NULL
srs_id: NULL
*************************** 2. row ***************************
table_catalog: greptime
table_schema: public
table_name: t1
column_name: v
ordinal_position: 2
character_maximum_length: NULL
character_octet_length: NULL
numeric_precision: 22
numeric_scale: NULL
datetime_precision: NULL
character_set_name: NULL
collation_name: NULL
column_key:
extra:
privileges: select,insert
generation_expression:
greptime_data_type: Float64
data_type: double
semantic_type: FIELD
column_default: NULL
is_nullable: Yes
column_type: double
column_comment: NULL
srs_id: NULL
*************************** 3. row ***************************
table_catalog: greptime
table_schema: public
table_name: t1
column_name: ts
ordinal_position: 3
character_maximum_length: NULL
character_octet_length: NULL
numeric_precision: NULL
numeric_scale: NULL
datetime_precision: 3
character_set_name: NULL
collation_name: NULL
column_key: TIME INDEX
extra:
privileges: select,insert
generation_expression:
greptime_data_type: TimestampMillisecond
data_type: timestamp(3)
semantic_type: TIMESTAMP
column_default: current_timestamp()
is_nullable: No
column_type: timestamp(3)
column_comment: NULL
srs_id: NULL
3 rows in set (0.03 sec)
The description of columns in the COLUMNS table is as follows:
table_catalog: The name of the catalog to which the table with the column belongs. The value is alwaysgreptimein OSS project.table_schema: The name of the database in which the table with the column is located.table_name: The name of the table with the column.column_name: The name of the column.ordinal_position: The position of the column in the table.character_maximum_length: For string columns, the maximum length in characters.character_octet_length: For string columns, the maximum length in bytes.numeric_precision: The precision of the column for numeric data types.numeric_scale: The scale of the column for numeric data types.datetime_precision: The fractional seconds precision of the column for datetime data types.character_set_name: The name of the character set of a string column.collation_name: The name of the collation of a string column.column_key: The key type of the column. It can be one of the following:PRI,TIME INDEX, or an empty string.extra: Additional information about the column.privileges: The privilege that the current user has on this column.generation_expression: For generated columns, this value displays the expression used to calculate the column value. For non-generated columns, the value is empty.greptime_data_type: The GreptimeDB data type of the column.data_type: The type of data in the column.semantic_type: The type of the column. It can be one of the following:TAG,FIELD, orTIMESTAMP.column_default: The default value of the column. If the explicit default value isNULL, or if the column definition does not include thedefaultclause, this value isNULL.is_nullable: Whether the column is nullable. If the column can store null values, this value isYES; otherwise, it isNO.column_type: The data type of the column. It is the same as theDATA_TYPEcolumn.column_comment: Comments contained in the column definition.srs_id: The ID of the spatial reference system (SRS) of the column.
The corresponding SHOW statement is as follows:
SHOW COLUMNS FROM t1 FROM public;
The output is as follows:
+-------+--------------+------+------------+---------------------+-------+----------------------+
| Field | Type | Null | Key | Default | Extra | Greptime_type |
+-------+--------------+------+------------+---------------------+-------+----------------------+
| h | string | Yes | PRI | NULL | | String |
| ts | timestamp(3) | No | TIME INDEX | current_timestamp() | | TimestampMillisecond |
| v | double | Yes | | NULL | | Float64 |
+-------+--------------+------+------------+---------------------+-------+----------------------+
3 rows in set (0.01 sec)