Cast collection data types

In some cases, you can use an explicit cast to perform conversions between two collections with different element types. To compare or substitute between values of any two collection types, both collections must be of type SET, MULTISET, or LIST.

Two element types are equivalent when all component types are the same. For example, if the element type of one collection is a row type, the other collection type is also a row type with the same number of fields and the same field data types.
Casts exist in the database to perform conversions between any and all components of the element types that are not of the same data type.
If the corresponding element types are not of the same data type, can use either built-in casts or user-defined casts to handle data conversions on the element types.

When the database server inserts, updates, or compares values of a collection data type, type checking occurs at the level of the element data type. Consequently, in a cast between two collection types, the data conversion occurs at the level of the element type because the actual data stored in a collection is of a particular element type.

The following type and tables are used in the collection casting examples in this section:

CREATE DISTINCT TYPE my_int AS INT;

CREATE TABLE set_tab1 (col1 SET(my_int NOT NULL));
CREATE TABLE set_tab2 (col2 SET(INT NOT NULL));
CREATE TABLE set_tab3 (col3 SET(FLOAT NOT NULL));
CREATE TABLE list_tab (col4 LIST(INT NOT NULL));
CREATE TABLE m_set_tab(col5 MULTISET(INT NOT NULL));