Using Explicit Casts with Distinct Types

To compare or substitute between values of a distinct type and its source type, you must explicitly cast one type to the other. For example, to insert into or update a column of a distinct type with values of the source type, you must explicitly cast the values to the distinct type.

Suppose you create a distinct type, int_type, that is based on the INTEGER data type and a table with a column of type int_type, as follows:

CREATE DISTINCT TYPE int_type AS INTEGER;
CREATE TABLE tab_z(col1 int_type); 

To insert a value into the tab_z table, you must explicitly cast the value for the col1 column to int_type, as follows:

INSERT INTO tab_z VALUES (35::int_type)

Suppose you create a distinct type, num_type, that is based on the NUMERIC, data type and a table with a column of type num_type, as follows:

CREATE DISTINCT TYPE num_type AS NUMERIC;
CREATE TABLE tab_x (col1 num_type);

The distinct num_type inherits none of the system-defined casts that exist for the NUMERIC data type. Consequently, the following insert requires two levels of casting. The first cast converts the value 35 from INT to NUMERIC and the second cast converts from NUMERIC to num_type:

INSERT INTO tab_x VALUES (35::NUMERIC::num_type)

The following INSERT statement on the tab_x table returns an error because no cast exists to convert directly from an INT type to num_type:

INSERT INTO tab_x VALUES (70::num_type) -- returns error