Contents

ENUM

A customisable datatype comprising a static, ordered set of values

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

Use DROP TYPE ... CASCADE:

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

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 |

Categories

Data type

See also

pg_enum, CREATE TYPE, ALTER TYPE, DROP TYPE