CREATE TYPE
Synopsis
Use the CREATE TYPE statement to create a user-defined type in a database. There are five types: composite, enumerated, range, base, and shell. Each has its own CREATE TYPE syntax.
Syntax
create_composite_type ::= CREATE TYPE type_name AS (
[ composite_type_elem [ , ... ] ] )
create_enum_type ::= CREATE TYPE type_name AS ENUM (
[ label [ , ... ] ] )
create_range_type ::= CREATE TYPE type_name AS RANGE ( SUBTYPE =
subtype [ , range_type_option [ ... ] ] )
create_base_type ::= CREATE TYPE type_name ( INPUT = input_function ,
OUTPUT = output_function
[ , base_type_option [ ... ] ] )
create_shell_type ::= CREATE TYPE type_name
composite_type_elem ::= attribute_name data_type [ COLLATE collation ]
range_type_option ::= SUBTYPE_OPCLASS = subtype_operator_class
| COLLATION = collation
| CANONICAL = canonical_function
| SUBTYPE_DIFF = subtype_diff_function
base_type_option ::= RECEIVE = receive_function
| SEND = send_function
| TYPMOD_IN = type_modifier_input_function
| TYPMOD_OUT = type_modifier_output_function
| INTERNALLENGTH = { internallength | VARIABLE }
| PASSEDBYVALUE
| ALIGNMENT = alignment
| STORAGE = storage
| LIKE = like_type
| CATEGORY = category
| PREFERRED = preferred
| DEFAULT = default
| ELEMENT = element
| DELIMITER = delimiter
| COLLATABLE = collatable
create_composite_type
create_enum_type
create_range_type
create_base_type
create_shell_type
composite_type_elem
range_type_option
base_type_option
Semantics
The order of options in creating range types and base types does not matter. Even the mandatory options SUBTYPE, INPUT, and OUTPUT may appear in any order.
create_composite_type
create_enum_type
create_range_type
create_base_type
create_shell_type
composite_type_elem
range_type_option
base_type_option
type_namespecifies the name of this user-defined type.attribute_namespecifies the name of an attribute for this composite type.data_typespecifies the type of an attribute for this composite type.collationspecifies the collation to use for this type. In case this is a composite type, the attribute data type must be collatable. In case this is a range type, the subtype must be collatable.labelspecifies a quoted label to be a value of this enumerated type.subtypespecifies the type to use for this range type.subtype_operator_classspecifies the operator class to use for the subtype of this range type.canonical_functionspecifies the canonical function used when converting range values of this range type to a canonical form.subtype_diff_functionspecifies the subtype difference function used to take the difference between two range values of this range type.input_functionspecifies the function that converts this type's external textual representation to internal representation.output_functionspecifies the function that converts this type's internal representation to external textual representation.receive_functionspecifies the function that converts this type's external binary representation to internal representation.send_functionspecifies the function that converts this type's internal representation to external binary representation.type_modifier_input_functionspecifies the function that converts this type modifier's external textual representation to internal integer typmod value or throws an error.type_modifier_output_functionspecifies the function that converts this type modifier's internal integer typmod value to external representation.internallengthspecifies the size in bytes of this type.alignmentspecifies the storage alignment of this type.storagespecifies the storage strategy of this type. This type must be variable length.like_typespecifies the type to copy over theINTERNALLENGTH,PASSEDBYVALUE,ALIGNMENT, andSTORAGEvalues from.categoryspecifies the category code for this type.preferredspecifies whether this type is preferred for implicit casts in the same category.defaultspecifies the default value of this type.elementspecifies the elements this type, also making this type an array.delimiterspecifies the character used to separate array elements in the external textual representation of values of this type.collatablespecifies whether collation information may be passed to operations that use this type.
Examples
Composite type
yugabyte=# CREATE TYPE feature_struct AS (id INTEGER, name TEXT);
yugabyte=# CREATE TABLE feature_tab_struct (feature_col feature_struct);
Enumerated type
yugabyte=# CREATE TYPE feature_enum AS ENUM ('one', 'two', 'three');
yugabyte=# CREATE TABLE feature_tab_enum (feature_col feature_enum);
Range type
yugabyte=# CREATE TYPE feature_range AS RANGE (subtype=INTEGER);
yugabyte=# CREATE TABLE feature_tab_range (feature_col feature_range);
Base type
yugabyte=# CREATE TYPE int4_type;
yugabyte=# CREATE FUNCTION int4_type_in(cstring) RETURNS int4_type
LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE AS 'int4in';
yugabyte=# CREATE FUNCTION int4_type_out(int4_type) RETURNS cstring
LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE AS 'int4out';
yugabyte=# CREATE TYPE int4_type (
INPUT = int4_type_in,
OUTPUT = int4_type_out,
LIKE = int4
);
yugabyte=# CREATE TABLE int4_table (t int4_type);
Shell type
yugabyte=# CREATE TYPE shell_type;