ENUM

A user-defined ENUM data type consists of a set of enumerated, static values.

Syntax

To declare a new enumerated 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. 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:

> SHOW ENUMS;

To modify an ENUM type, use ALTER TYPE:

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

> 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

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

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:

> SELECT * FROM accounts WHERE status::STRING='open';

                   id                  | balance | status
---------------------------------------+---------+---------
  3848e36d-ebd4-44c6-8925-8bf24bba957e |  500.50 | open
(1 row)

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

> CREATE TYPE inaccessible AS ENUM ('closed', 'inactive');

> CREATE TABLE notifications (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        status inaccessible,
        message STRING
);

> INSERT INTO notifications(status, message) VALUES ('closed', 'This account has been closed.'),('inactive', 'This account is on hold.');

> SELECT
    accounts.id, notifications.message
    FROM accounts JOIN notifications ON accounts.status = notifications.status;

ERROR: unsupported comparison operator: <status> = <inaccessible>
SQLSTATE: 22023

> SELECT
    accounts.id, notifications.message
    FROM accounts JOIN notifications ON accounts.status::STRING = notifications.status;

ERROR: unsupported comparison operator: <string> = <inaccessible>
SQLSTATE: 22023

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

Get Started

Syntax

Required privileges

Example

Supported casting and conversion

Comparing enumerated types

See also

​Syntax

​Required privileges

​Example

​Supported casting and conversion

​Comparing enumerated types

​See also

Syntax

Required privileges

Example

Supported casting and conversion

Comparing enumerated types

See also