Informix Guide to SQL: Syntax SQL Statements

Use the CREATE DISTINCT TYPE statement to create a new distinct type. A distinct type is a data type based on a built-in type or an existing opaque type, a named-row type, or another distinct type. Distinct types are strongly typed. Although the distinct type has the same physical representation as data of its source type, the two types cannot be compared without an explicit cast from one type to the other.

To create a distinct type in a database, you must have the Resource privilege. Any user with the Resource privilege can create a distinct type from one of the built-in data types, which are owned by user informix.

Important: You cannot create a distinct type on the SERIAL or SERIAL8 data type.

To create a distinct type from an opaque type, a named-row type, or another distinct type, you must be the owner of the type or have the Usage privilege on the type.

Once a distinct type is defined, only the type owner and the DBA can use it. The owner of the type can grant other users the Usage privilege on the type.

A distinct type has the same storage structure as its source type.The following statement creates the distinct type birthday, based on the built-in data type, DATE:

CREATE DISTINCT TYPE birthday AS DATE

Dynamic Server uses the same storage method for the distinct type as it does for the source type of the distinct type. However, a distinct type and its source type cannot be compared in an operation unless one type is explicitly cast to the other type.

To create a distinct type, you must have the Resource privilege on the database. When you create the distinct type, only you, the owner, have Usage privilege on this type. Use the GRANT or REVOKE statements to grant or revoke Usage privilege to other database users.

To find out what privileges exist on a particular type, check the sysxtdtypes system catalog table for the owner name and the sysxtdtypeauth system catalog table for additional type privileges that might have been granted. For more information on system catalog tables, see the Informix Guide to SQL: Reference.

The DB-Access utility can also display privileges on distinct types.

When you create a distinct type, Dynamic Server automatically defines two explicit casts:

• A cast from the distinct type to its source type
• A cast from the source type to the distinct type

Because the two types have the same representation (the same length and alignment), no support functions are required to implement the casts.

You can create an implicit cast between a distinct type and its source type. However, to create an implicit cast, you must first drop the default explicit cast between the distinct type and its source type.

All support functions and casts that are defined on the source type can be used on the distinct type. However, casts and support functions that are defined on the distinct type are not available to the source type.

When you compare or manipulate data of a distinct type and its source type, you must explicitly cast one type to the other.

You must explicitly cast one type to the other in the following situations:

• To insert or update a column of one type with values of the other type
• To use a relational operator to add, subtract, multiply, divide, compare, or otherwise manipulate two values, one of the source type and one of the distinct type

For example, suppose you create a distinct type, dist_type, that is based on the NUMERIC data type. You then create a table with two columns, one of type dist_type and one of type NUMERIC.

CREATE DISTINCT TYPE dist_type AS NUMERIC;
CREATE TABLE t(col1 dist_type, col2 NUMERIC);

To directly compare the distinct type and its source type or assign a value of the source type to a column of the distinct type, you must cast one type to the other, as the following examples show:

INSERT INTO tab (col1) VALUES (3.5::dist_type);


SELECT col1, col2 

	FROM t WHERE (col1::NUMERIC) > col2;


SELECT col1, col2, (col1 + col2::dist_type) sum_col 

	FROM tab;

Related statements: CREATE CAST, CREATE FUNCTION, CREATE OPAQUE TYPE, CREATE ROW TYPE, DROP TYPE and DROP ROW TYPE

For information and examples that show how to use and cast distinct types, see the Informix Guide to SQL: Tutorial.

For more information on when you might create a distinct type, see Extending Informix Dynamic Server 2000.