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

# ENUM

A [user-defined `ENUM` data type](/docs/stable/create-type#create-an-enumerated-data-type) consists of a set of enumerated, static values.

## Syntax

To declare a new enumerated data type, use [`CREATE TYPE`](/docs/stable/create-type#create-an-enumerated-data-type):

```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 [database and schema name](/docs/stable/sql-name-resolution.md) (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](/docs/stable/multiregion-overview.md), use [`SHOW ENUMS`](/docs/stable/show-enums):

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

To modify an `ENUM` type, use [`ALTER TYPE`](/docs/stable/alter-type):

```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 [role specification](/docs/stable/grant.md).

To drop the type, use [`DROP TYPE`](/docs/stable/drop-type):

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

## Required privileges

* To [create a type](/docs/stable/create-type.md) in a database, a user must have the `CREATE` [privilege](/docs/stable/security-reference/authorization.md#managing-privileges) on the database.
* To [drop a type](/docs/stable/drop-type.md), a user must be the owner of the type.
* To [alter a type](/docs/stable/alter-type.md), a user must be the owner of the type.
* To [grant privileges](/docs/stable/grant.md) 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;
```

```
  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;
```

```
                   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;
```

```
  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 [cast](/docs/stable/data-types.md#data-type-conversions-and-casts) to [`STRING`s](/docs/stable/string).

Values can be cast explicitly or implicitly. For example, the following [`SELECT`](/docs/stable/select-clause) statements are equivalent:

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

```
                   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';
```

```
                   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;
```

```
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;
```

```
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;
```

```
                   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

* [Data Types](/docs/stable/data-types.md)
* [`CREATE TYPE`](/docs/stable/create-type)
* [`ALTER TYPE`](/docs/stable/alter-type)
* [`SHOW ENUMS`](/docs/stable/show-enums)
* [`DROP TYPE`](/docs/stable/drop-type)