ENUM

Warning:
Cockroach Labs will stop providing Assistance Support for this version on November 18, 2022. Prior to that date, upgrade to a more recent version to continue receiving support. For more details, see the Release Support Policy.

User-defined ENUM data types consist of a set of enumerated, static values.

Warning:

Clusters with ENUM types that are running alpha testing releases of v20.2 will not be able to upgrade to beta testing releases or production releases of v20.2 due to internal representation changes.

Syntax

To declare a new ENUM data type, use CREATE TYPE:

> CREATE TYPE <name> AS ENUM ('<value1>', '<value2>', ...);

where <name> is the name of the new type, and <value1>, <value2>, ... are string literals that make up the type's set of static values.

Note:

You can qualify the <name> of an enumerated type with a database and schema name (e.g., db.typename). After the type is created, it can only be referenced from the database that contains the type.

To show all ENUM types in the database, including all ENUMS created implicitly for multi-region databases, use SHOW ENUMS:

icon/buttons/copy
> SHOW ENUMS;

To modify an ENUM type, use ALTER TYPE:

icon/buttons/copy
> ALTER TYPE <name> ADD VALUE '<value>';

where <value> is a string literal to add to the existing list of type values. You can also use ALTER TYPE to rename types, rename type values, set a type's schema, or change the type owner's role specification.

To drop the type, use DROP TYPE:

icon/buttons/copy
> DROP TYPE <name>;

Required privileges

  • To create a type in a database, a user must have the CREATE privilege on the database.
  • To drop a type, a user must be the owner of the type.
  • To alter a type, a user must be the owner of the type.
  • To grant privileges on a type, a user must have the GRANT privilege and the privilege that they want to grant.
  • To create an object that depends on a type, a 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 "primary" PRIMARY KEY (id ASC),
             |     FAMILY "primary" (id, balance, status)
             | )
(1 row)

Supported casting and conversion

ENUM data type values can be cast to STRINGs.

Values can be cast explicitly or implicitly. For example, the following SELECT statements are equivalent:

icon/buttons/copy
> SELECT * FROM accounts WHERE status::STRING='open';
                   id                  | balance | status
---------------------------------------+---------+---------
  3848e36d-ebd4-44c6-8925-8bf24bba957e |  500.50 | open
(1 row)
icon/buttons/copy
> SELECT * FROM accounts WHERE status='open';
                   id                  | balance | status
---------------------------------------+---------+---------
  3848e36d-ebd4-44c6-8925-8bf24bba957e |  500.50 | open
(1 row)

Comparing enumerated types

To compare two enumerated types, you must explicitly cast both types as STRINGs. For example:

icon/buttons/copy
> CREATE TYPE inaccessible AS ENUM ('closed', 'inactive');
icon/buttons/copy
> CREATE TABLE notifications (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        status inaccessible,
        message STRING
);
icon/buttons/copy
> INSERT INTO notifications(status, message) VALUES ('closed', 'This account has been closed.'),('inactive', 'This account is on hold.');
icon/buttons/copy
> SELECT
    accounts.id, notifications.message
    FROM accounts JOIN notifications ON accounts.status = notifications.status;
ERROR: unsupported comparison operator: <status> = <inaccessible>
SQLSTATE: 22023
icon/buttons/copy
> SELECT
    accounts.id, notifications.message
    FROM accounts JOIN notifications ON accounts.status::STRING = notifications.status;
ERROR: unsupported comparison operator: <string> = <inaccessible>
SQLSTATE: 22023
icon/buttons/copy
> SELECT
    accounts.id, notifications.message
    FROM accounts JOIN notifications ON accounts.status::STRING = notifications.status::STRING;
                   id                  |            message
---------------------------------------+--------------------------------
  285336c4-ca1f-490d-b0df-146aae94f5aa | This account is on hold.
  583157d5-4f34-43e5-a4d4-51db77feb391 | This account has been closed.
(2 rows)

See also

YesYes NoNo