On this page 
The CREATE TYPE statement creates a new type in a database. After the type is created, it can only be referenced from the database that contains the type.
The following user-defined data types can be created with this statement:
Note:
The CREATE TYPE statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.
Synopsis
Parameters
| Parameter | Description |
|---|---|
type_name | The name of the type. You can qualify the name with a database and schema name (e.g., db.typename), but after the type is created, it can only be referenced from the database that contains the type. |
IF NOT EXISTS | Create a new type only if a type of the same name does not already exist in the database; if one does exist, do not return an error. |
opt_enum_val_list | A list of values that make up the type's enumerated set. |
opt_composite_type_list | A list of values that make up the set of types that make up a composite type. |
Required privileges
- To create a type, the user must have the
CREATEprivilege on the parent database and the schema in which the type is being created. - To use a user-defined type in a table (e.g., when defining a column's type), the user must have the
USAGEprivilege on the type.
Example
Create an enumerated data type
Use the statements below to create an ENUM data type.
> CREATE TYPE IF NOT EXISTS status AS ENUM ('open', 'closed', 'inactive'); To see all user-defined data types, use SHOW TYPES:
> SHOW TYPES; schema | name | owner ---------+--------+-------- public | status | root (1 row) To see the values accepted by the underlying ENUM data type, use SHOW ENUMS:
> SHOW ENUMS; schema | name | values | owner ---------+--------+------------------------+-------- public | status | {open,closed,inactive} | root (1 row) > CREATE TABLE IF NOT EXISTS accounts ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), balance DECIMAL, status status ); > INSERT INTO accounts(balance,status) VALUES (500.50,'open'), (0.00,'closed'), (1.25,'inactive'); > SELECT * FROM accounts; id | balance | status ---------------------------------------+---------+----------- 3848e36d-ebd4-44c6-8925-8bf24bba957e | 500.50 | open 60928059-ef75-47b1-81e3-25ec1fb6ff10 | 0.00 | closed 71ae151d-99c3-4505-8e33-9cda15fce302 | 1.25 | inactive (3 rows) > SHOW CREATE TABLE accounts; table_name | create_statement -------------+-------------------------------------------------- accounts | CREATE TABLE public.accounts ( | id UUID NOT NULL DEFAULT gen_random_uuid(), | balance DECIMAL NULL, | status public.status NULL, | CONSTRAINT accounts_pkey PRIMARY KEY (id ASC) | ) (1 row) > SELECT * FROM accounts WHERE status='open'; id | balance | status ---------------------------------------+---------+--------- 3848e36d-ebd4-44c6-8925-8bf24bba957e | 500.50 | open (1 row) Create a composite data type
Use the statements below to create a composite data type:
CREATE TYPE IF NOT EXISTS my_point AS (x INT, y INT); CREATE TABLE IF NOT EXISTS points ( id UUID DEFAULT gen_random_uuid(), p my_point, CONSTRAINT my_point_xcheck CHECK (((p).x >= 0) AND ((p).x <= 10000)), CONSTRAINT my_point_ycheck CHECK (((p).y >= 0) AND ((p).y <= 10000)) ); Insert 10,000 randomly generated values of type mypoint:
INSERT INTO points (p) SELECT (floor(random()*10000),floor(random()*10000))::MY_POINT FROM generate_series(1,10000); SELECT * FROM points ORDER BY ((p).x) ASC LIMIT 25; id | p ---------------------------------------+------------ 434095ae-6297-490a-9040-278c749b4d29 | (0,9982) 4f3155f6-d184-415b-98af-22b061da6623 | (1,5242) ff3eda0a-c144-43c4-9bbc-0c14e90a0349 | (1,4053) 8a97e5f1-9db6-4458-8d3a-172f8624560c | (7,9857) 1a599ed3-0a86-4096-851b-d5b35fb9cea4 | (7,599) 5fc17aef-6de4-452c-a434-a709b2210938 | (9,9786) 0967799c-c726-4f42-915e-4517ea3c589b | (9,458) 6c1a46a9-66f8-4e96-8501-49fe6f77490c | (10,1447) a10824ec-3d17-4162-8457-372a76675a84 | (10,808) e7edf3fb-88f6-4675-9c79-44f74b7d0f49 | (11,1654) fd477aec-6685-4607-a940-7d4b1608913b | (11,6563) 5806fcb5-28b6-4a94-b8dc-229fadb1d46d | (12,5519) a9545c0c-2b45-4e1f-bb19-e9a7a1630d6f | (12,4474) d591111b-a1c1-4168-8565-41f3d914dc6d | (12,7164) 262482e5-d58d-463a-8a1d-ed66389e9424 | (15,3047) dfd75b68-1da0-4ea1-8e6e-66a9dd7389da | (16,1306) 060070ee-40df-4c97-badb-9c08a2f54209 | (16,3016) 33b77621-596b-4ee3-98e9-127ba942f1a7 | (16,2343) f3c7e11a-1784-4604-b083-e78dd32ab8b4 | (17,5155) 485d45ae-0870-47b6-b986-4fabf0207644 | (17,4715) 319f2184-3743-4fb2-8fe7-8efb0710bbee | (19,8570) ca55a46a-e5b6-4da5-8be5-575a39cea07c | (19,6667) f1ef70f6-0fb7-4338-b488-14cc6187e61b | (20,9113) 3b18c682-967b-408d-8877-4ac102da3f12 | (21,5736) d0dd5914-a353-4603-a9a8-494f68db01f9 | (21,4328) (25 rows)