Postgres Enum

What is ENUM? Why do we use it? Where is it used? How to use ENUM in PostgreSQL? What is the purpose of ENUM in the statement? Well, all these questions arise in your mind when you hear the word ENUM for the first time. Let this article help you in answering these questions. ENUM, or you can say Enumerated also, is a data type held by PostgreSQL. It is used to store predefined values in a system; similar types of values can also be stored with the help of ENUM.

We first need to create the type of ENUM using CREATE TYPE command to use that in the table; the purpose of this command is to store static values or ordered set of values in PostgreSQL.

If we want to use similar values in a table, we can define more than one value in the ENUM type. A simple example of an ENUM type can be storing colors in a rainbow or, similarly, some values to store data. The most basic syntax for creating an ENUM type is:

A table can be created using the enum_type too and here is a basic syntax for creating a table using an ENUM type:

The table is now created to insert some values in it you must use INSERT INTO command with value of ENUM data type to add values in the columns:

If you want to change the table, you can use the ALTER TYPE command to alter the table and add values to the table. By this, you can alter your created ENUM type and simply add the value to an ENUM data type. This syntax will add value to your table by adding an ENUM type to your table:

ENUM Type Examples in PostgreSQL:

ENUM types are good for abstraction purposes. If we put ENUM in simpler words, then using ENUM, we create a new data type and allot it to the specific values. Let’s see an illustration of how we can use ENUM type in PostgreSQL. But for using ENUM in the table, we first need to create an ENUM type.

Creating an ENUM Type:

For using ENUM in the table we first need to create an ENUM type. Underneath is the syntax for creating an ENUM type:

In the above statement, CREATE TYPE command is used to create an ENUM data type in PostgreSQL. After the CREATE TYPE command, specify your ENUM name that can be used in the table as a data type for a column. AS keyword is written with the ENUM keyword after the ENUM name, this is “cars_model” in our statement. After the ENUM keyword set of values is specified in brackets ( ) with single inverted commas separated with commas after each value. You can specify more than one value that is used as an ENUM data type in the table.

The PostgreSQL shows a message after running the above command as:

Creating a Table Using ENUM Type in PostgreSQL:

Now that the ENUM type is created, we can create a table in PostgreSQL, which we can use as an ENUM type. The following code will create a table using ENUM type in PostgreSQL:

The above statement will create a table named “Cars” in the database with columns name “brand” as ‘cars_model’ data type that we created using ENUM, “color” as ‘TEXT ’ and “car_number” as ‘INTEGER’ data types. Once we have used the ENUM data type in a column, we can not use any other value in that column that was not in the ENUM data type.

When the table “Cars” is created, it will show the following output:

To confirm that the table is created, we will run the SELECT query to display the table with specified columns:

This query will show the following output on the screen:

Insert Values into Table Using ENUM data type in PostgreSQL:

We have created the table “Cars” in the above example; now, we can insert some values in it using ENUM data types using the following syntax:

I have inserted a single row in the table with three values that will show this message:

To confirm the rows are inserted, run the SELECT query:

Note, if you insert any other value in the ENUM data type column, then PostgreSQL will display an error message. Let’s see an example of that:

As we can see, it says “invalid input value for ENUM cars_model: “Ford”, this means if you enter values other than you specify in the ENUM type statement, it will display an error.

Conclusion:

This guide covered the steps of how to implement ENUM data type in PostgreSQL. In this guide, we have discussed the ENUM in detail with code as well. ENUM data types are static and ordered values in which you can create the same type of data set values and use them in the table. To use them in a table, you first need to create an ENUM type using CREATE TYPE command with ENUM. Then, you can create a table where you can use that ENUM type in the columns as your data types; you can alter or drop values as well in it.