What is an Enum in PostgreSQL?

Postgres enum (enumerated types) is an important data type used to store the predefined list of values in the columns. They make it easier to query and sort the data and implement good standards of data integrity. It is important to keep enums maintained and organized to avoid probable issues.

This write-up will explain what is an enum and how it works in PostgreSQL.

What is an Enum in PostgreSQL?

Enums are a very powerful feature of Postgresql that allows us to store some predefined set of values in the column. In order to use an enum in a table, we have to define it first and create the type of enum first. We can also define multiple values of the enum type to use it in the table. In Postgres, the CREATE TYPE command is used to create enums. The syntax to create an enum is illustrated below:

CREATE TYPE nameOf_enumType (valueOf_enumType1, valueOf_enumType2, valueOf_enumType3, ..., valueOf_enumTypeN);

The CREATE TYPE is followed by the name of the enum type which is followed by the list of values of enum types.

Syntax

Consider the example of project status, we can create an enum named “project_status” with the following syntax:

CREATE TYPE project_status as enum('In progress', 'Completed', 'tested', 'Cancelled');

We can also create a table using an enum type using the following syntax:

Create table nameOf_table (nameOf_column1 datatype, nameOf_column2 enumtype, nameOf_column3 datatype, ..., nameOf_columnN datatype);

To insert a value of the enum data type column we use the given syntax:

INSERT INTO nameOf_table (nameOf_column1, name0f_enumtype_column2, nameOf_column3, ...,   name0f_columnN) values (value1, valueOf_enumtype, value2, value3, ...., ValueN);

We will understand all of the syntaxes thoroughly through examples.

How Does Postgres Enum Work?

We will now discuss the working of enum in Postgres. The basic principle is that if we want to use an enum type, we will need to create it first. Without creating an enum type we won’t be able to use it in the table, instead, we will get an error.

Let’s consider the same example of project status to demonstrate the concept. Consider the following query:

CREATE TABLE project_status(Name text, Status enumType, Managed_by text);

In the above query:

We have created a table where an enum type is used.
The name of the table is “project_status”, which is followed by the list of column names with their datatypes.
The first and last column name is “name” and “managed_by” respectively and the datatype is text.
The second column named “status” has data type enumType. Now can you make a guess about what the output of this query should be? Will it return a table with 3 columns?

No, it won’t. The reason for this is, that whenever we need to use an enum type we have to create it first.

Output

We can clearly see that we haven’t created the enum type. So now have a look at the output for the query:

We can see that the output is an error, clearly saying that we have not created enumType. Now let’s create an enum type with the following query:

CREATE TYPE enumType AS ENUM ('In progress', 'Completed', 'tested', 'Cancelled');

The output ensures the successful creation of enumType:

Now if we again create the table as in the first query, what do you think, will it create the table? Or will it still return an error? The answer is “yes”, a table will be created as follows:

So, we can see that the table is created with the 3 columns “name”, “status”, and “managed_by” with their data types text, enumType, and text respectively.

We will now insert some values into the table”project_status”:

INSERT INTO project_status VALUES ('Game app', 'In progress', 'John');
 INSERT INTO project_status VALUES ('Chat application', 'Completed', 'Williams');
 INSERT INTO project_status VALUES ('Online Food ordering App', 'tested', 'sarah');
 SELECT * FROM   project_status;

This is what the INSERT query returns:

Enum Functions in PostgreSQL

There are various methods and operations that we can apply on enums while querying data:

● enum_first() - This function returns the first enumerator of an enum type.

● enum_last() - This function gives the last enumerator of an enum type.

● enum_range() - This function returns an array containing all the enumerators for the enum type in the same order as defined.

Drawbacks of Enums in PostgreSQL

Another important fact about enums is that enums are not flexible at all. They do not possess the ability to be altered once they are created. In short, the enums are fixed in nature. This fact can sometimes be a downside of an enum if we want to add or delete a value from the enum type.

Also, if we want the value of an enum data type to be changed, we will have to create a new enum type, shift the data to that newly created enum type, and then we will have to update/change the reference to that old enum type. Which is a quite hectic and time-consuming task

Conclusion

Enums are useful data types in Postgres that make it much easier to query, sort, and maintain the data and eventually improve data integrity. We can use enum types anywhere after creating the enum type. Despite the fact that enums help in maintaining data integrity, we should also consider the fact that enums do have some limitations. so we always have to choose the data type that fulfills our needs completely.

What is an Enum in PostgreSQL?