A distinct type is based on a predefined data type.
ExampleCREATE TYPE size AS INTEGER;
This creates a distinct type. When a distinct type is created, there is an implicit creation of a function for converting a value of the type on which the user-defined type is based to the user-defined type. By default this function has the same name as the user-defined type.
ExampleCREATE TABLE container (name NCHAR VARYING(30), length size); INSERT INTO container VALUES ('Large x450', size(450));
In addition, when the user-defined type is created, a function for converting to the base type is created. This function has the same name as the base data type.
ExampleSELECT name, integer(length) FROM container;
It is not possible to compare two instances of different distinct types. This is regardless of if the types on which the distinct types are based are comparable or not. Also, it is not possible to declare an instance of a distinct type with a value of the type on which the distinct type is based. I.e. a statement likeSELECT name FROM container WHERE length = 450;
is not valid. To do this comparison, either value need to be converted:SELECT name FROM container WHERE integer(length) = 450; SELECT name FROM container WHERE length = size(450);
It is possible to override the default naming of the implicitly created function and method. This is done by using the following syntax:
ExampleCREATE TYPE size AS integer CAST(source as distinct) WITH cast_from_int_to_size CAST(distinct as source) WITH cast_from_size_to_int; INSERT INTO container VALUES ('Large x450', cast_from_int_to_size(450)); SELECT name, cast_from_size_to_int(length) FROM container;
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40