COLUMNS

The COLUMNS provides detailed information about columns in tables.

  1. USE INFORMATION_SCHEMA;
  2. DESC COLUMNS;

The output is as follows:

  1. +--------------------------+--------+------+------+---------+---------------+
  2. | Column                   | Type   | Key  | Null | Default | Semantic Type |
  3. +--------------------------+--------+------+------+---------+---------------+
  4. | table_catalog            | String |      | NO   |         | FIELD         |
  5. | table_schema             | String |      | NO   |         | FIELD         |
  6. | table_name               | String |      | NO   |         | FIELD         |
  7. | column_name              | String |      | NO   |         | FIELD         |
  8. | ordinal_position         | Int64  |      | NO   |         | FIELD         |
  9. | character_maximum_length | Int64  |      | YES  |         | FIELD         |
  10. | character_octet_length   | Int64  |      | YES  |         | FIELD         |
  11. | numeric_precision        | Int64  |      | YES  |         | FIELD         |
  12. | numeric_scale            | Int64  |      | YES  |         | FIELD         |
  13. | datetime_precision       | Int64  |      | YES  |         | FIELD         |
  14. | character_set_name       | String |      | YES  |         | FIELD         |
  15. | collation_name           | String |      | YES  |         | FIELD         |
  16. | column_key               | String |      | NO   |         | FIELD         |
  17. | extra                    | String |      | NO   |         | FIELD         |
  18. | privileges               | String |      | NO   |         | FIELD         |
  19. | generation_expression    | String |      | NO   |         | FIELD         |
  20. | greptime_data_type       | String |      | NO   |         | FIELD         |
  21. | data_type                | String |      | NO   |         | FIELD         |
  22. | semantic_type            | String |      | NO   |         | FIELD         |
  23. | column_default           | String |      | YES  |         | FIELD         |
  24. | is_nullable              | String |      | NO   |         | FIELD         |
  25. | column_type              | String |      | NO   |         | FIELD         |
  26. | column_comment           | String |      | YES  |         | FIELD         |
  27. | srs_id                   | Int64  |      | YES  |         | FIELD         |
  28. +--------------------------+--------+------+------+---------+---------------+
  29. 24 rows in set (0.00 sec)

Create a table public.t1 and query the information in the COLUMNS table:

  1. CREATE TABLE public.t1 (h STRING, v FLOAT64, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP() TIME INDEX, PRIMARY KEY(h));
  2. SELECT * FROM COLUMNS WHERE table_schema='public' AND TABLE_NAME='t1'\G

The output is as follows:

  1. *************************** 1. row ***************************
  2.            table_catalog: greptime
  3.             table_schema: public
  4.               table_name: t1
  5.              column_name: h
  6.         ordinal_position: 1
  7. character_maximum_length: 2147483647
  8.   character_octet_length: 2147483647
  9.        numeric_precision: NULL
  10.            numeric_scale: NULL
  11.       datetime_precision: NULL
  12.       character_set_name: utf8
  13.           collation_name: utf8_bin
  14.               column_key: PRI
  15.                    extra:
  16.               privileges: select,insert
  17.    generation_expression:
  18.       greptime_data_type: String
  19.                data_type: string
  20.            semantic_type: TAG
  21.           column_default: NULL
  22.              is_nullable: Yes
  23.              column_type: string
  24.           column_comment: NULL
  25.                   srs_id: NULL
  26. *************************** 2. row ***************************
  27.            table_catalog: greptime
  28.             table_schema: public
  29.               table_name: t1
  30.              column_name: v
  31.         ordinal_position: 2
  32. character_maximum_length: NULL
  33.   character_octet_length: NULL
  34.        numeric_precision: 22
  35.            numeric_scale: NULL
  36.       datetime_precision: NULL
  37.       character_set_name: NULL
  38.           collation_name: NULL
  39.               column_key:
  40.                    extra:
  41.               privileges: select,insert
  42.    generation_expression:
  43.       greptime_data_type: Float64
  44.                data_type: double
  45.            semantic_type: FIELD
  46.           column_default: NULL
  47.              is_nullable: Yes
  48.              column_type: double
  49.           column_comment: NULL
  50.                   srs_id: NULL
  51. *************************** 3. row ***************************
  52.            table_catalog: greptime
  53.             table_schema: public
  54.               table_name: t1
  55.              column_name: ts
  56.         ordinal_position: 3
  57. character_maximum_length: NULL
  58.   character_octet_length: NULL
  59.        numeric_precision: NULL
  60.            numeric_scale: NULL
  61.       datetime_precision: 3
  62.       character_set_name: NULL
  63.           collation_name: NULL
  64.               column_key: TIME INDEX
  65.                    extra:
  66.               privileges: select,insert
  67.    generation_expression:
  68.       greptime_data_type: TimestampMillisecond
  69.                data_type: timestamp(3)
  70.            semantic_type: TIMESTAMP
  71.           column_default: current_timestamp()
  72.              is_nullable: No
  73.              column_type: timestamp(3)
  74.           column_comment: NULL
  75.                   srs_id: NULL
  76. 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 always greptime in 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, or TIMESTAMP.
  • column_default: The default value of the column. If the explicit default value is NULL, or if the column definition does not include the default clause, this value is NULL.
  • is_nullable: Whether the column is nullable. If the column can store null values, this value is YES; otherwise, it is NO.
  • column_type: The data type of the column. It is the same as the DATA_TYPE column.
  • 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:

  1. SHOW COLUMNS FROM t1 FROM public;

The output is as follows:

  1. +-------+--------------+------+------------+---------------------+-------+----------------------+
  2. | Field | Type         | Null | Key        | Default             | Extra | Greptime_type        |
  3. +-------+--------------+------+------------+---------------------+-------+----------------------+
  4. | h     | string       | Yes  | PRI        | NULL                |       | String               |
  5. | ts    | timestamp(3) | No   | TIME INDEX | current_timestamp() |       | TimestampMillisecond |
  6. | v     | double       | Yes  |            | NULL                |       | Float64              |
  7. +-------+--------------+------+------------+---------------------+-------+----------------------+
  8. 3 rows in set (0.01 sec)