On this page 
The CREATE TYPE statement creates a new, enumerated data type in a database.
Note:
The CREATE TYPE statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.
Note:
CockroachDB currently only supports enumerated user-defined types.
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. |
Required privileges
- To create a type, the user must have the
CREATEprivilege on the parent database. - 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 TYPE status AS ENUM ('open', 'closed', 'inactive'); > SHOW ENUMS; schema | name | value ---------+--------+----------------------- public | status | open|closed|inactive (1 row) > CREATE TABLE 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)