Introduction to PostgreSQL enum Data Type

The PostgreSQL enum data type is a data type used to define enumerated types. It allows you to define a named list of possible values and associate this list with a column or a table cell. In PostgreSQL, an enum type is essentially a labeled set of integer values, which are user-defined.

Syntax

The syntax for creating an enum type is as follows:

CREATE TYPE enum_type AS ENUM ('label1', 'label2', ...);

where enum_type is the user-defined type name, followed by a comma-separated list of labels.

The syntax for defining a column with an enum type is as follows:

column_name enum_type

The syntax for defining an enum column in a table is as follows:

CREATE TABLE table_name (
    ...
    column_name enum_type,
    ...
);

Use Cases

The enum data type is suitable for cases where you have a predefined set of possible values. For example, if your application needs to store values for red, green, and blue colors in a column, you can define an enum type to represent these colors and apply it to that column. Using an enum data type ensures that only predefined values are inserted into the column, which can prevent data inconsistency.

Another use case for the enum type is when you need to use statuses in your application. For example, if your application needs to maintain order statuses, you can define an enum type that includes possible statuses for orders, such as “pending”, “paid”, “accepted”, “shipped”, “completed”, and apply it to the status column in the orders table.

Examples

Example 1

Here is an example of an enum type:

CREATE TYPE color AS ENUM ('red', 'green', 'blue');

In the above example, we create an enum type named color that includes three colors.

We can use this type to define a column in a table, for example:

CREATE TABLE products (
    ...
    color color,
    ...
);

Next, we can insert records with predefined color values, for example:

INSERT INTO products (name, price, color)
VALUES ('Product 1', 10.99, 'red'),
       ('Product 2', 15.99, 'green'),
       ('Product 3', 20.99, 'blue');

Example 2

Here is an example of an enum type used for order statuses:

CREATE TYPE order_status AS ENUM ('pending', 'paid', 'accepted', 'shipped', 'completed');

In the above example, we create an enum type named order_status that includes possible order statuses.

We can use this type to define a status column in an orders table, for example:

CREATE TABLE orders (
    ...
    status order_status,
    ...
);

Next, we can update the order status to ‘paid’ or ‘cancelled’ and view the data in the table.

UPDATE orders SET status = 'paid' WHERE id = 1;
UPDATE orders SET status = 'cancelled' WHERE id = 3;
SELECT * FROM orders;

After executing the above SQL statement, we can obtain the following result:

 id | customer_id |  amount   |   status
----+-------------+-----------+-------------
  1 |           1 | 1000.00   | paid
  2 |           2 | 2000.00   | pending
  3 |           3 | 3000.00   | cancelled
(3 rows)

We can see that the values in the status column have been updated to either “paid” or “cancelled”.

Conclusion

PostgreSQL’s enum data type provides a convenient way to represent columns with a fixed set of values. It can be defined as a column type in a table, can contain one or more possible values, and can be specified during table creation or added through table structure modification. The enum data type can be used in various scenarios, such as for status columns, category columns, etc. In queries, operators such as = and <> can be used to compare enum values. Additionally, enum values can be added, deleted, or renamed by modifying the enum type.