Collections with Different Element Types

How you handle conversions between two collections that have the same collection type but different element types depends on the element type of each collection and the type of cast that the database server uses to convert one element type to another when the element types are different, as follows:

• If a built-in cast or implicit user-defined cast exists to handle the conversion between two element types, you do not need to explicitly cast between the collection types.
• If an explicit cast exists to handle the conversion between element types, you can perform an explicit cast on a collection.

Using an Implicit Cast Between Element Types

When an implicit cast exists in the database to convert between different element types of two collections, you do not need to use an explicit cast to insert or update elements from one collection into another collection. The following INSERT statement retrieves elements from the set_tab2 table and inserts the elements into the set_tab3 table. Although the collection column from set_tab2 has an INT element type and the collection column from set_tab3 has a FLOAT element type, a built-in cast implicitly handles the conversion between INT and FLOAT values. An explicit cast is unnecessary in this case.

INSERT INTO set_tab3 SELECT col2 FROM set_tab2

Using an Explicit Cast Between Element Types

When a conversion between different element types of two collections is performed with an explicit cast, you must explicitly cast one collection to the other collection type. In the following example, the conversion between the element types (INT and my_int) requires an explicit cast. (A cast between a distinct type and its source type is always explicit).

The following INSERT statement retrieves elements from the set_tab2 table and inserts the elements into the set_tab1 table. The collection column from set_tab2 has an INT element type, and the collection column from set_tab1 has a my_int element type. Because the conversion between the element types (INT and my_int) requires an explicit cast, you must explicitly cast the collection type.

INSERT INTO set_tab1 SELECT col2::SET(my_int NOT NULL) 
   FROM set_tab2

To perform an explicit cast on a collection type, you must include the constructor (SET, MULTISET, or LIST), the element type, and the NOT NULL keyword.