PostgreSQL Enum Types

This article describes the PostgreSQL enum data type and its usage.

Introduction to PostgreSQL Enum types

In PostgreSQL, an Enum type is an ordered set of constant values. For example, you can use an enum type to define the status value of an order.

Without an enum type, you might use integers 1, 2, 3 ... or strings representation, but it’s easy to produce some errors. After using the enum type, the database can guarantee that the wrong value cannot be stored.

Create PostgreSQL Enum value

To use an enum value, you need to create a custom enum value using the CREATE TYPE statement.

Here is the syntax for creating an enum type:

CREATE TYPE name AS ENUM (
  label_1
  [, label_2, ... ]
);

Explanation:

  • name is the enum name to create.
  • label_1 and label_2 are constant values of the enum type.

PostgreSQL Enum Examples

First, create an enum type representing week days using the following statement:

CREATE TYPE my_week AS ENUM (
    'Monday',
    'Tuesday',
    'Wednesday',
    'Thursday',
    'Friday',
    'Saturday',
    'Sunday'
);

Then, create a table named week_day_sales to store sales of week days using the following statement,

CREATE TABLE week_day_sales (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    week_day my_week NOT NULL,
    sales integer NOT NULL
);

Then, insert several new rows using the following statement:

INSERT INTO week_day_sales (week_day, sales)
VALUES
    ('Monday', 110),
    ('Tuesday', 120),
    ('Wednesday', 130),
    ('Thursday', 140),
    ('Friday', 150),
    ('Saturday', 160),
    ('Sunday', 170),
    ('Monday', 210),
    ('Tuesday', 220),
    ('Wednesday', 230),
    ('Thursday', 240),
    ('Friday', 250),
    ('Saturday', 260),
    ('Sunday', 270);

If you insert an incorrect value into an enum column, PostgreSQL will give an error, as follows:

INSERT INTO week_day_sales (week_day, sales)
VALUES ('Error', 110);

ERROR:  invalid input value for enum my_week: "Error"
LINE 2: VALUES ('Error', 110);

The following statement retrieves all the rows of the week_day_sales table :

SELECT * FROM week_day_sales;

 id | week_day  | sales
----+-----------+-------
  1 | Monday    |   110
  2 | Tuesday   |   120
  3 | Wednesday |   130
  4 | Thursday  |   140
  5 | Friday    |   150
  6 | Saturday  |   160
  7 | Sunday    |   170
  8 | Monday    |   210
  9 | Tuesday   |   220
 10 | Wednesday |   230
 11 | Thursday  |   240
 12 | Friday    |   250
 13 | Saturday  |   260
 14 | Sunday    |   270
(14 rows)

To sort by the values ​​of an enum type, use the following statement:

SELECT * FROM week_day_sales ORDER BY week_day;

 id | week_day  | sales
----+-----------+-------
  1 | Monday    |   110
  8 | Monday    |   210
  2 | Tuesday   |   120
  9 | Tuesday   |   220
  3 | Wednesday |   130
 10 | Wednesday |   230
  4 | Thursday  |   140
 11 | Thursday  |   240
  5 | Friday    |   150
 12 | Friday    |   250
  6 | Saturday  |   160
 13 | Saturday  |   260
  7 | Sunday    |   170
 14 | Sunday    |   270
(14 rows)

PostgreSQL Enum functions

PostgreSQL provides some functions for enum types as follows:

  • The enum_first() function returns the first enum value of the enum type specified by the parameter.
  • The enum_last() function returns the last enum value of the enum type specified by the parameter.
  • The enum_range() function returns all enum values ​​of the enum type specified by the parameter, or the enum values ​​in the specified range.

Conclusion

In PostgreSQL, an enum type is an ordered set of constant values.