COMPLEX_TYPES

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

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)