User-Defined Types: Group Fields Together
Some fields always belong together. An address has a street, a city and a zip code. You can group such fields into your own type, called a user-defined type (UDT). Then you use it like any other column type.
Define the type
CREATE TYPE lists the fields and their types, like a small table:
CREATE TYPE address (
street text,
city text,
zip text
);
Use it in a table
CREATE TABLE contacts (
id int PRIMARY KEY,
name text,
home frozen<address>
);
frozen<...> means Cassandra stores the address as one single value. You always write the whole address at once. You can't change only one field of it. A UDT column without frozen lets you change one field, but this course uses frozen because it is simpler.
Insert a UDT value
Write the value in curly braces, with field: value pairs:
INSERT INTO contacts (id, name, home)
VALUES (1, 'Ann', {
street: '12 Oak Ave',
city: 'Springfield',
zip: '11111'
});
SELECT * FROM contacts;
Output:
id | home | name
----+-----------------------------------------------------------+------
1 | {street: '12 Oak Ave', city: 'Springfield', zip: '11111'} | Ann
(1 rows)
It looks like a map, but the field names have no quotes. They are part of the type, not text values. The fields come back in the order of CREATE TYPE, not sorted.
If you put quotes around the field names, Cassandra reads the value as a map, and the insert fails:
INSERT INTO contacts (id, name, home)
VALUES (2, 'Bo', {
'street': '5 Elm St',
'city': 'Shelbyville',
'zip': '22222'
});
Output:
InvalidRequest: Error from server: code=2200 [Invalid query] message="Invalid map literal for home of type frozen<address>"
Read one field
Use a dot to read one field:
SELECT name, home.city FROM contacts
WHERE id = 1;
Output:
name | home.city
------+-------------
Ann | Springfield
(1 rows)
Change an address
Because home is frozen, you can't set only home.city:
UPDATE contacts
SET home.city = 'Shelbyville'
WHERE id = 1;
Output:
InvalidRequest: Error from server: code=2200 [Invalid query] message="Invalid operation (home.city = 'Shelbyville') for frozen UDT column home"
Write the whole new address instead:
UPDATE contacts
SET home = {
street: '9 Pine Rd',
city: 'Shelbyville',
zip: '33333'
}
WHERE id = 1;
SELECT * FROM contacts;
Output:
id | home | name
----+----------------------------------------------------------+------
1 | {street: '9 Pine Rd', city: 'Shelbyville', zip: '33333'} | Ann
(1 rows)
When to use a UDT
A UDT keeps a table tidy. One column home frozen<address> is easier to read than three columns home_street, home_city and home_zip. UDTs also work inside collections. For example, addresses map<text, frozen<address>> can hold a 'home' address and a 'work' address. Keep UDTs small, and don't put types inside types inside types.
In the exercise, insert a new contact with a full address.