CREATE DISTINCT TYPE statement

Use the CREATE DISTINCT TYPE statement to create a new distinct data type.

This statement is an extension to the ANSI/ISO standard for SQL.

Syntax

CREATE DISTINCT TYPE [IF NOT EXISTS] distinct_type ASsource_type


Element	Description	Restrictions	Syntax
distinct_type	Name that you declare here for the new distinct data type	In an ANSI-compliant database, the combination of the owner and data type must be unique within the database. In a database that is not ANSI compliant, the name must be unique among names of data types in the database.	Data Type
source_type	Name of existing type on which the new type is based	Must be either a built-in data type or one created with the CREATE DISTINCT TYPE, CREATE OPAQUE TYPE, or CREATE ROW TYPE statement	Data Type

Usage

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

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

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

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

By default, after a distinct type is defined, only the owner of the distinct type and the DBA can use it. The owner of the distinct type, however, can grant to other users the Usage privilege on the distinct 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 DATE data type:

CREATE DISTINCT TYPE birthday AS DATE;

Although Informix® uses the same storage format for the distinct type as it does for its source type, a distinct type and its source type cannot be compared in an operation unless one type is cast explicitly to the other type.

If you include the optional IF NOT EXISTS keywords, the database server takes no action (rather than sending an exception to the application) if a DISTINCT data type of the specified name is already registered in the current database.