New in v20.2: User-defined ENUM data types consist of a set of enumerated, static values.
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.
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
CREATEprivilege 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
GRANTprivilege and the privilege that they want to grant. - To create an object that depends on a type, a user must have the
USAGEprivilege on the type.
Known limitations
Partitions cannot be created on columns of type ENUM. See tracking issue.
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 "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:
> 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
Was this helpful?
