Creating a user-defined type (UDT)

User-Defined Types (UDTs) can be used to attach multiple data fields to a column.

User-defined types (UDTs) can attach multiple data fields, each named and typed, to a single column. The fields used to create a UDT may be any valid data type, including collections and other existing UDTs. After a UDT is created, it can be used to define a table column.

Procedure

  • Create a user-defined type named basic_info.
    CREATE TYPE IF NOT EXISTS cycling.basic_info ( 
      birthday timestamp, 
      nationality text, 
      height text,
      weight text
    );
  • Create a table for storing cyclist data in columns of type basic_info. Use the frozen keyword in the definition of the user-defined type column.

    The frozen keyword is not required for UDTs that contain only non-collection fields.

    When using the frozen keyword, you cannot update parts of a user-defined type value. The entire value must be overwritten. The database treats the value of a frozen, user-defined type like a blob.

    CREATE TABLE IF NOT EXISTS cycling.cyclist_stats ( 
      id UUID PRIMARY KEY, 
      lastname text, 
      basics basic_info
    );
  • A user-defined type can be nested in another column type. The example below nests a UDT in a frozen list named races.
    CREATE TYPE IF NOT EXISTS cycling.race (
      race_title text,
      race_date timestamp,
      race_time text
    );
    CREATE TABLE IF NOT EXISTS cycling.cyclist_races (
      id UUID PRIMARY KEY,
      lastname text,
      firstname text,
      races list<FROZEN <race>>
    );