> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# ENUM

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

A <InternalLink path="create-type#create-an-enumerated-data-type">user-defined `ENUM` data type</InternalLink> consists of a set of enumerated, static values.

## Syntax

To declare a new enumerated data type, use <InternalLink path="create-type#create-an-enumerated-data-type">`CREATE TYPE`</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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.

You can qualify the `<name` of an enumerated type with a <InternalLink path="sql-name-resolution">database and schema name</InternalLink> (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 <InternalLink path="multiregion-overview">multi-region databases</InternalLink>, use <InternalLink path="show-enums">`SHOW ENUMS`</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW ENUMS;
```

To modify an `ENUM` type, use <InternalLink path="alter-type">`ALTER TYPE`</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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 <InternalLink path="grant">role specification</InternalLink>.

To drop the type, use <InternalLink path="drop-type">`DROP TYPE`</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> DROP TYPE <name>;
```

## Required privileges

* To <InternalLink path="create-type">create a type</InternalLink> in a database, a user must have the `CREATE` <InternalLink path="security-reference/authorization#managing-privileges">privilege</InternalLink> on the database.
* To <InternalLink path="drop-type">drop a type</InternalLink>, a user must be the owner of the type.
* To <InternalLink path="alter-type">alter a type</InternalLink>, a user must be the owner of the type.
* To <InternalLink path="grant">grant privileges</InternalLink> 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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TYPE status AS ENUM ('open', 'closed', 'inactive');
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW ENUMS;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  schema |  name  |        value
---------+--------+-----------------------
  public | status | open|closed|inactive
(1 row)
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE accounts (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        balance DECIMAL,
        status status
);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO accounts(balance,status) VALUES (500.50,'open'), (0.00,'closed'), (1.25,'inactive');
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM accounts;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
                   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)
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW CREATE TABLE accounts;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  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)
```

## Supported casting and conversion

`ENUM` data type values can be <InternalLink path="data-types#data-type-conversions-and-casts">cast</InternalLink> to <InternalLink path="string">`STRING`s</InternalLink>.

Values can be cast explicitly or implicitly. For example, the following <InternalLink path="select-clause">`SELECT`</InternalLink> statements are equivalent:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM accounts WHERE status::STRING='open';
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
                   id                  | balance | status
---------------------------------------+---------+---------
  3848e36d-ebd4-44c6-8925-8bf24bba957e |  500.50 | open
(1 row)
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM accounts WHERE status='open';
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
                   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 `STRING`s. For example:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TYPE inaccessible AS ENUM ('closed', 'inactive');
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE notifications (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        status inaccessible,
        message STRING
);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO notifications(status, message) VALUES ('closed', 'This account has been closed.'),('inactive', 'This account is on hold.');
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT
    accounts.id, notifications.message
    FROM accounts JOIN notifications ON accounts.status = notifications.status;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ERROR: unsupported comparison operator: <status> = <inaccessible>
SQLSTATE: 22023
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT
    accounts.id, notifications.message
    FROM accounts JOIN notifications ON accounts.status::STRING = notifications.status;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ERROR: unsupported comparison operator: <string> = <inaccessible>
SQLSTATE: 22023
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT
    accounts.id, notifications.message
    FROM accounts JOIN notifications ON accounts.status::STRING = notifications.status::STRING;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
                   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

* <InternalLink path="data-types">Data Types</InternalLink>
* <InternalLink path="create-type">`CREATE TYPE`</InternalLink>
* <InternalLink path="alter-type">`ALTER TYPE`</InternalLink>
* <InternalLink path="show-enums">`SHOW ENUMS`</InternalLink>
* <InternalLink path="drop-type">`DROP TYPE`</InternalLink>