COMPLEX_TYPES

Contains information about inlined complex types.

Each complex type in each external table has a unique type internally, even if the types are structurally the same (like two different ROW(int,int) cases). This inlined type is created when the table using it is created and is automatically dropped when the table is dropped. Inlined complex types cannot be shared or reused in other tables.

Each row in the COMPLEX_TYPES table represents one component (field) in one complex type. A ROW produces one row per field, an ARRAY produces one, and a MAP produces two.

Arrays of primitive types used in native (ROS) tables are not included in the COMPLEX_TYPES table. They are included instead in the TYPES table.

This table does not include complex types in Iceberg tables.

Column Name	Data Type	Description
`TYPE_ID`	INTEGER	A unique identifier for the inlined complex type.
`TYPE_KIND`	VARCHAR	The specific kind of complex type: row, array, or map.
`TYPE_NAME`	VARCHAR	The generated name of this type. All names begin with _ct_ followed by a number.
`FIELD_ID`	INTEGER	A unique identifier for the field.
`FIELD_NAME`	VARCHAR	The name of the field, if specified in the table definition, or a generated name beginning with "f".
`FIELD_TYPE_NAME`	VARCHAR	The type of the field's value.
`FIELD_POSITION`	INTEGER	The field's position in its containing complex type (0-based).
`FIELD_LENGTH`	INTEGER	Number of bytes in the field value, or -1 if the value is not a scalar type.
`CHARACTER_MAXIMUM_LENGTH`	INTEGER	Maximum allowable length of the column.
`NUMERIC_PRECISION`	INTEGER	Number of significant decimal digits.
`NUMERIC_SCALE`	INTEGER	Number of fractional digits.
`DATETIME_PRECISION`	INTEGER	For `TIMESTAMP` data type, returns the declared precision; returns NULL if no precision was declared.
`INTERVAL_PRECISION`	INTEGER	Number of fractional digits retained in the seconds field.

Examples

The following example shows the type and field values after defining a single external table.

=> CREATE EXTERNAL TABLE warehouse(
    name VARCHAR, id_map MAP<INT,VARCHAR>,
    data row(record INT, total FLOAT, description VARCHAR(100)),
    prices ARRAY[INT], comment VARCHAR(200), sales_total FLOAT, storeID INT)
  AS COPY FROM ... PARQUET;

=> SELECT type_id,type_kind,type_name,field_id,field_name,field_type_name,field_position
    FROM COMPLEX_TYPES ORDER BY type_id,field_name;

      type_id      | type_kind |       type_name       | field_id | field_name  | field_type_name | field_position
-------------------+-----------+-----------------------+----------+-------------+-----------------+----------------
 45035996274278280 | Map       | _ct_45035996274278280 |        6 | key         | int             |              0
 45035996274278280 | Map       | _ct_45035996274278280 |        9 | value       | varchar(80)     |              1
 45035996274278282 | Row       | _ct_45035996274278282 |        9 | description | varchar(80)     |              2
 45035996274278282 | Row       | _ct_45035996274278282 |        6 | record      | int             |              0
 45035996274278282 | Row       | _ct_45035996274278282 |        7 | total       | float           |              1
 45035996274278284 | Array     | _ct_45035996274278284 |        6 |             | int             |              0
(6 rows)