COMPLEX_TYPES

Contains information about inlined complex types (Complex Types).

Contains information about inlined complex types (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.

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)