New in v20.2: 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, use SHOW ENUMS:

copy
icon/buttons/copy
> SHOW ENUMS;

To modify an ENUM type, use ALTER TYPE:

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

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

Known limitations

Partitions cannot be created on columns of type ENUM. See tracking issue.

Example

copy
icon/buttons/copy
> CREATE TYPE status AS ENUM ('open', 'closed', 'inactive');
copy
icon/buttons/copy
> SHOW ENUMS;
  schema |  name  |        value
---------+--------+-----------------------
  public | status | open|closed|inactive
(1 row)
copy
icon/buttons/copy
> CREATE TABLE accounts (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        balance DECIMAL,
        status status
);
copy
icon/buttons/copy
> INSERT INTO accounts(balance,status) VALUES (500.50,'open'), (0.00,'closed'), (1.25,'inactive');
copy
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)
copy
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:

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

copy
icon/buttons/copy
> CREATE TYPE inaccessible AS ENUM ('closed', 'inactive');
copy
icon/buttons/copy
> CREATE TABLE notifications (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        status inaccessible,
        message STRING
);
copy
icon/buttons/copy
> INSERT INTO notifications(status, message) VALUES ('closed', 'This account has been closed.'),('inactive', 'This account is on hold.');
copy
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
copy
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
copy
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