On this page 
Warning:
CockroachDB v22.2 is no longer supported as of June 5, 2024. For more details, refer to the Release Support Policy.
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)
See also
Was this helpful?
