ENUM

A consists of a set of enumerated, static values.

Syntax

To declare a new enumerated data type, use :

> 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 (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 , use :

> SHOW ENUMS;

To modify an ENUM type, use :

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

To drop the type, use :

> DROP TYPE <name>;

Required privileges

• To in a database, a user must have the CREATE on the database.
• To , a user must be the owner of the type.
• To , a user must be the owner of the type.
• To 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 to .

Values can be cast explicitly or implicitly. For example, the following 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)

See also