ENUM
ENUM
is a customisable datatype comprising a static, ordered set of values.
The ENUM
datatype was added in PostgreSQL 8.3.
Creating ENUM types
Creating an ENUM type
Use CREATE TYPE
to create an ENUM
type:
CREATE TYPE enum_test AS ENUM ('foo', 'bar', 'baz');
Not that each value in the ENUM
type can contain up to 63 bytes (NAMEDATALEN
- 1) including spaces and non-ASCII characters.
Using an ENUM type in a table definition
Use the ENUM
type's name like any other type:
CREATE TABLE thing ( thing_id INT NOT NULL, thing_type enum_test );
Modifying ENUM types
Adding a value
From PostgreSQL 9.1, values can be added to existing ENUM
types using ALTER TYPE
, e.g.:
ALTER TYPE enum_test ADD VALUE 'boo'; ALTER TYPE enum_test ADD VALUE 'bee' BEFORE 'boo';
From PostgreSQL 9.3, values can be added to existing ENUM
types in an idempotent manner using the IF NOT EXISTS
clause, e.g.:
ALTER TYPE enum_test ADD VALUE IF NOT EXISTS 'boo';
Renaming a value
From PostgreSQL 10, values in existing ENUM
types can be renamed using ALTER TYPE
, e.g.:
ALTER TYPE enum_test RENAME VALUE 'boo' TO 'baa';
Removing a value
It is not possible to remove a value from an ENUM
type.
Changing the order of values
It is not possible to change the order of values in an ENUM
type.
Dropping ENUM types
Dropping an unused ENUM type
Use DROP TYPE
:
DROP TYPE enum_other;
Dropping an ENUM type used in a table
DROP TYPE enum_test CASCADE;
But be aware this will drop all column(s) where this ENUM
is used.
psql command
\dT+ typename
will display details of the ENUM
type including all values it contains. See below for example.
Change history
- PostgreSQL 10
- values in an existing
ENUM
type can be renamed (commit 0ab9c56d)
- values in an existing
- PostgreSQL 9.3
- values can be added to existing
ENUM
types in an idempotent manner using theIF NOT EXISTS
clause (commit 6d12b68c)
- values can be added to existing
- PostgreSQL 9.1
- new values can be added to an existing
ENUM
type (commit 84c123be)
- new values can be added to an existing
- PostgreSQL 8.3
- added (commit 57690c68)
Examples
Create an ENUM
type:
postgres=# CREATE TYPE enum_test AS ENUM ('foo', 'bar', 'baz'); CREATE TYPE postgres=# \dT List of data types Schema | Name | Description --------+-----------+------------- public | enum_test | (1 row) postgres=# \dT+ List of data types Schema | Name | Internal name | Size | Elements | Owner | Access privileges | Description --------+-----------+---------------+------+----------+----------+-------------------+------------- public | enum_test | enum_test | 4 | foo +| postgres | | | | | | bar +| | | | | | | baz | | | (1 row)
Create a table using an ENUM
type:
postgres=# CREATE TABLE thing (thing_id INT NOT NULL, thing_type enum_test); CREATE TABLE postgres=# \d thing Table "public.thing" Column | Type | Collation | Nullable | Default ------------+-----------+-----------+----------+--------- thing_id | integer | | not null | thing_type | enum_test | | |
Inserting data into an ENUM
type:
postgres=# INSERT INTO thing values(1,'foo'); INSERT 0 1 postgres=# INSERT INTO thing values(2,'bar'); INSERT 0 1 postgres=# INSERT INTO thing values(1,'baa'); ERROR: invalid input value for enum enum_test: "baa" LINE 1: INSERT INTO thing values(1,'baa');
Selecting an ENUM
type:
postgres=# SELECT * FROM thing; thing_id | thing_type ----------+------------ 1 | foo 2 | bar (2 rows) postgres=# SELECT * FROM thing WHERE thing_type='bar'; thing_id | thing_type ----------+------------ 1 | bar (1 row) postgres=# SELECT * FROM thing WHERE thing_type='baa'; ERROR: invalid input value for enum enum_test: "baa" LINE 1: SELECT * FROM thing WHERE thing_type='baa'; ^
Dropping an ENUM
type:
postgres=# DROP TYPE enum_other; DROP TYPE postgres=# DROP TYPE enum_test; ERROR: cannot drop type enum_test because other objects depend on it DETAIL: column thing_type of table thing depends on type enum_test postgres=# DROP TYPE enum_test CASCADE; NOTICE: drop cascades to column thing_type of table thing DROP TYPE postgres=# \d thing Table "public.thing" Column | Type | Collation | Nullable | Default ----------+---------+-----------+----------+--------- thing_id | integer | | not null |
References
- PostgreSQL documentation: Enumerated Types
Useful links
- Enums vs Check Constraints in Postgres - December 2022 blog article by Craig Kerstiens / Crunchy Data
- Find all enum columns in PostgreSQL database - June 2019 blog article by Bart Gawrych / Dataedo
- Looking up Enum types and values in Postgres - May 2019 blog article by Sadique Ali Koothumadan
- Enumerated Data Types - Postgres Guide