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_type ::= create_composite_type
                | create_enum_type
                | create_range_type
                | create_shell_type
                | create_base_type

create_composite_type ::= CREATE TYPE type_name AS ( 
                          [ composite_type_elem [ , ... ] ] )

create_enum_type ::= CREATE TYPE type_name AS ENUM ( 
                     [ name [ , ... ] ] )

create_range_type ::= CREATE TYPE type_name AS RANGE  ( SUBTYPE = 
                      subtype [ , range_type_option [ ... ] ] )

create_shell_type ::= CREATE TYPE type_name

create_base_type ::= CREATE TYPE type_name (  INPUT = input_function , 
                      OUTPUT = output_function 
                     [ , base_type_option [ ... ] ]  )

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 = { TRUE | FALSE }
                     | DEFAULT = default_type_value
                     | ELEMENT = element
                     | DELIMITER = delimiter
                     | COLLATABLE = { TRUE | FALSE }

create_type

create_composite_typecreate_enum_typecreate_range_typecreate_shell_typecreate_base_type

create_composite_type

CREATETYPEtype_nameAS(,composite_type_elem)

create_enum_type

CREATETYPEtype_nameASENUM(,name)

create_range_type

CREATETYPEtype_nameASRANGE(SUBTYPE=subtype,range_type_option)

create_shell_type

CREATETYPEtype_name

create_base_type

CREATETYPEtype_name(INPUT=input_function,OUTPUT=output_function,base_type_option)

composite_type_elem

attribute_namedata_typeCOLLATEcollation

range_type_option

SUBTYPE_OPCLASS=subtype_operator_classCOLLATION=collationCANONICAL=canonical_functionSUBTYPE_DIFF=subtype_diff_function

base_type_option

RECEIVE=receive_functionSEND=send_functionTYPMOD_IN=type_modifier_input_functionTYPMOD_OUT=type_modifier_output_functionINTERNALLENGTH=internallengthVARIABLEPASSEDBYVALUEALIGNMENT=alignmentSTORAGE=storageLIKE=like_typeCATEGORY=categoryPREFERRED=TRUEFALSEDEFAULT=default_type_valueELEMENT=elementDELIMITER=delimiterCOLLATABLE=TRUEFALSE

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_name specifies the name of this user-defined type.
  • attribute_name specifies the name of an attribute for this composite type.
  • data_type specifies the type of an attribute for this composite type.
  • collation specifies 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.
  • label specifies a quoted label to be a value of this enumerated type.
  • subtype specifies the type to use for this range type.
  • subtype_operator_class specifies the operator class to use for the subtype of this range type.
  • canonical_function specifies the canonical function used when converting range values of this range type to a canonical form.
  • subtype_diff_function specifies the subtype difference function used to take the difference between two range values of this range type.
  • input_function specifies the function that converts this type's external textual representation to internal representation.
  • output_function specifies the function that converts this type's internal representation to external textual representation.
  • receive_function specifies the function that converts this type's external binary representation to internal representation.
  • send_function specifies the function that converts this type's internal representation to external binary representation.
  • type_modifier_input_function specifies the function that converts this type modifier's external textual representation to internal integer typmod value or throws an error.
  • type_modifier_output_function specifies the function that converts this type modifier's internal integer typmod value to external representation.
  • internallength specifies the size in bytes of this type.
  • alignment specifies the storage alignment of this type.
  • storage specifies the storage strategy of this type. This type must be variable length.
  • like_type specifies the type to copy over the INTERNALLENGTH, PASSEDBYVALUE, ALIGNMENT, and STORAGE values from.
  • category specifies the category code for this type.
  • PREFERRED specifies whether this type is preferred for implicit casts in the same category.
  • default specifies the default value of this type.
  • element Implies that the type being created is an array; this specifies the type of the array elements.
  • delimiter specifies the character used to separate array elements in the external textual representation of values of this type.
  • COLLATABLE specifies 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;

See also