User-Defined Types (UDT)

A user-defined type facilitates handling multiple fields of related information in a table. Applications that required multiple tables can be simplified to use fewer tables by using a user-defined type to represent the related fields of information instead of storing the information in a separate table.

CREATE TYPE

Creates a custom data type in the keyspace that contains one or more fields of related information, such as address (street, city, state, and postal code). Restriction: UDTs cannot contain counter fields.

CREATE KEYSPACE IF NOT EXISTS cycling WITH replication = {'class':'SimpleStrategy', 'replication_factor' : 1};
USE cycling;

CREATE TYPE basic_info (
  birthday timestamp,
  nationality text,
  weight text,
  height text
);

ALTER TYPE

Modifies an existing user-defined type (UDT).

Restriction: Modifying UDTs used in primary keys or index columns is not supported. Changing the field type is not supported.

Examples

Adding a field

ALTER TYPE basic_info ADD name text;

DESC TYPE basic_info;
CREATE TYPE cycling.basic_info (
    birthday timestamp,
    nationality text,
    weight text,
    height text,
    name text
);

Changing a field name

ALTER TYPE basic_info RENAME name TO fullname;

DESC TYPE basic_info;
CREATE TYPE cycling.basic_info (
    birthday timestamp,
    nationality text,
    weight text,
    height text,
    fullname text
);

DROP TYPE

Immediately and irreversibly removes a UDT (user-defined type).

Restriction: Dropping a user-defined type that is in use by a table or another type is not supported.

DROP TYPE [IF EXISTS] keyspace_name.type_name;

DROP TYPE basic_info;

DESC TYPE basic_info;
User type 'basic_info' not found

DESCRIBE TYPE

Lists user-defined types in the current keyspace or all user-defined types in the cluster when no keyspace is selected or provides CQL formatted details on the specified user-defined type.

DESCRIBE TYPES [keyspace_name] | TYPE [keyspace_name.]udt_name

DESC TYPES;

<empty>

CREATE TYPE basic_info (
  birthday timestamp,
  nationality text,
  weight text,
  height text
);

DESC TYPES;

basic_info

DESC TYPE basic_info;

CREATE TYPE cycling.basic_info (
    birthday timestamp,
    nationality text,
    weight text,
    height text
);

Inserting data into a UDT

If a table specifies a user-defined type (UDT) to hold data, then use INSERT or UPDATE to enter data.

Set or replace user-defined type data, using the INSERT or UPDATE command, and enclosing the user-defined type with curly brackets, separating each key-value pair in the user-defined type by a colon.

CREATE TABLE cyclist_stats(
id int PRIMARY KEY,
basics basic_info
);

INSERT INTO cyclist_stats (id, basics) VALUES (
  1, { birthday : '1993-06-18', nationality : 'New Zealand', weight : null, height : null }
);

SELECT * FROM cyclist_stats;

 id | basics
----+-------------------------------------------------------------------------------------------------------
  1 | {birthday: '1993-06-18 00:00:00.000000+0000', nationality: 'New Zealand', weight: null, height: null}

(1 rows)

SELECT basics.birthday FROM cyclist_stats;

 basics.birthday
---------------------------------
 1993-06-18 00:00:00.000000+0000

(1 rows)

CREATE TYPE race_info (
  race_title text,
  race_date text,
  race_time text
);

CREATE TABLE cyclist_races (
id int PRIMARY KEY,
races set<"race_info">
);
InvalidRequest: Error from server: code=2200 [Invalid query] message="Non-frozen UDTs are not allowed inside collections: set<race_info>"

CREATE TABLE cyclist_races (
id int PRIMARY KEY,
races set<frozen<"race_info">>
);

frozen makes explicit the fact that races value is unitary despite having an internal structure comprising multiple fields.

INSERT INTO cyclist_races (id, races) VALUES (
  1,
  {{ race_title : 'Rabobank 7-Dorpenomloop Aalburg',race_date : '2015-05-09',race_time : '02:58:33' },
  { race_title : 'Ronde van Gelderland',race_date : '2015-04-19',race_time : '03:22:23' }}
);

SELECT * FROM cyclist_races;

 id | races
----+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  1 | {{race_title: 'Rabobank 7-Dorpenomloop Aalburg', race_date: '2015-05-09', race_time: '02:58:33'}, {race_title: 'Ronde van Gelderland', race_date: '2015-04-19', race_time: '03:22:23'}}

Updating individual field data in a UDT

UPDATE cyclist_stats SET basics.birthday = '2000-12-12' WHERE id = 1;

SELECT * FROM cyclist_stats;

 id | basics
----+-------------------------------------------------------------------------------------------------------
  1 | {birthday: '2000-12-12 00:00:00.000000+0000', nationality: 'New Zealand', weight: null, height: null}

(1 rows)