What Is Enum Data Type in PostgreSQL?

What Is Enum Data Type in PostgreSQL?

When working with a PostgreSQL database, you may come across the term “enum” data type. The enum data type in PostgreSQL allows you to define a set of named values that can be used as a column type. This can be particularly useful when you want to restrict the possible values that can be stored in a column.

Creating an Enum Data Type

To create an enum data type in PostgreSQL, you need to use the CREATE TYPE statement. Let’s say we want to create an enum called “color” with three possible values: red, green, and blue. Here’s how you can do it:

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

In this example, we have created an enum data type called “color” with three possible values: red, green, and blue.

Using Enum Data Types in Columns

Once you have created an enum data type, you can use it as a column type when creating or altering tables. Let’s say we have a table called “cars” and we want to add a column called “paint_color” with the color enum data type. Here’s how you can do it:

CREATE TABLE cars (
   id serial PRIMARY KEY,
   make varchar(50),
   model varchar(50),
   paint_color color
);

In this example, we have added a column called “paint_color” with the color enum data type to the “cars” table.

Inserting Values into Enum Columns

To insert values into an enum column, you need to use one of the valid enum values that you defined when creating the enum data type. For example, if we want to insert a new car into the “cars” table with a paint color of red, we can do it like this:

INSERT INTO cars (make, model, paint_color) VALUES ('Toyota', 'Corolla', 'red');

In this example, we have inserted a new car into the “cars” table with a paint color of red.

Querying Enum Columns

When querying an enum column, you can use the valid enum values in your queries. For example, if we want to retrieve all cars from the “cars” table that are painted green, we can do it like this:

SELECT * FROM cars WHERE paint_color = 'green';

In this example, we are selecting all cars from the “cars” table where the paint color is green.

Conclusion

The enum data type in PostgreSQL allows you to define a set of named values that can be used as a column type. It provides a way to restrict the possible values that can be stored in a column.

By using the CREATE TYPE statement, you can create an enum data type and then use it as a column type when creating or altering tables. When inserting or querying values in an enum column, you need to use one of the valid enum values defined for that column.

The enum data type is just one of many powerful features offered by PostgreSQL that can help you manage and organize your data effectively.