CREATE TYPE

On this page Carat arrow pointing down
Warning:
As of November 24, 2023, CockroachDB v22.1 is no longer supported. 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

CREATE TYPE IF NOT EXISTS type_name AS ENUM ( opt_enum_val_list )

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 CREATE privilege 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 USAGE privilege on the type.

Example

icon/buttons/copy
> CREATE TYPE status AS ENUM ('open', 'closed', 'inactive');
icon/buttons/copy
> SHOW ENUMS;
  schema |  name  |        value
---------+--------+-----------------------
  public | status | open|closed|inactive
(1 row)
icon/buttons/copy
> CREATE TABLE accounts (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        balance DECIMAL,
        status status
);
icon/buttons/copy
> INSERT INTO accounts(balance,status) VALUES (500.50,'open'), (0.00,'closed'), (1.25,'inactive');
icon/buttons/copy
> 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)
icon/buttons/copy
> 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)
icon/buttons/copy
> SELECT * FROM accounts WHERE status='open';
                   id                  | balance | status
---------------------------------------+---------+---------
  3848e36d-ebd4-44c6-8925-8bf24bba957e |  500.50 | open
(1 row)

See also


Yes No
On this page

Yes No