Enums in PostgresSQL

Today we are going discuss about Enum datatype with practical examples.

Lord Shri Krishna is in tribanga pose with flute in hands #Bharatiya_Architecture

Today we will see how to :-

1. Create enum type in PostgresSql.
2. Usage of enum in PostgresSql.

Creating Enum type in PostgresSql:-

To use enum we have to create enum type in our database. We can leverage postgres feature where we can create our own type. Below is the syntax to create a new type in postgres.

Syntax:-

create type <type_name> as <datatype>;

With above syntax we will form the query to create enum type.

Example:-

create type gender as Enum('Male','Female');

As you can see in the above example we have created gender enum type. In this type we are giving two expected values as Male and Female.

Now we will use newly created type in our users table.

Example:-

ALTER TABLE public."Users" ADD user_gender gender NULL;

As you can see in the above example that we have added new column as user_gender and added it’s datatype as gender. Now we will see the usage of enum type.

Usage of enum in PostgresSql:-

The main advantage of using enum is that no other value can be inserted in the column which is not mentioned in the enum type. let’s see this with an example.

Example :-

insert into "Users"("Id" ,"Email","Password","Name","MobileNumber","user_gender") values
(123,'abhima@gmail.com','123','Abhima','9876543','Male');

Row got inserted successfully

insert into "Users"("Email","Password","Name","MobileNumber","user_gender") values
('abc@gmail.com','123','Abc','9876543','Other')

Error while inserting the data

In the first example we are inserting valid value in user_gender column that is mentioned in gender enum type as well. But in second example it is throwing error, as Other value is invalid for the column. So this way we can put restriction on particular column by giving it’s type as enum.

Comparison:-

We can use almost all comparison operators to compare enum values as below.

Example:-

select * from "Users" u where u.user_gender ='Male';

Successfully returned matching row.

As you can see in the above example that we have used equal(=) operator and retrieved all the records whose gender is Male.

Same way we can compare values from different enumerator as below.

Example:-

select * from "Users" u,"Employee" e  where u.user_gender::text =e.gender::text;

Output showing matching records from both the tables

In the above example we have compared gender of two different tables where enum type of both the tables were different enumerators.

Note:-

*Always convert the type to text while comparing two enumerators.

* Values are case sensitive.

Ordering:-

We can order the enum values. It will order the value on the basis of order in which it is defined in enum type while creating it.

select * from "Employee" e order by e.gender desc;

Output with Female is the first place

In the above example you can see the ordering of column where Female came first even after ordering it in descending order. The reason here is that enum gives constant integer value to the string value. That means in gender enum Male is having 0 and Female is having value as 1 and comparison is happening on those integer values. So if enum is Male and Female then in descending order Female records will come first.

Thank you for reading please comment your suggestions, share the article, follow me and Abhima Database Technology publication.

Bhagavad Gita: Chapter 9, Verse 3

अश्रद्दधाना: पुरुषा धर्मस्यास्य परन्तप |
अप्राप्य मां निवर्तन्ते मृत्युसंसारवर्त्मनि || 3||

Translation

People who have no faith in this dharma are unable to attain Me, O conqueror of enemies. They repeatedly come back to this world in the cycle of birth and death.

हे शत्रु विजेता! वे लोग जो इस धर्म में श्रद्धा नहीं रखते वे मुझे प्राप्त नहीं कर सकते। वे जन्म-मृत्यु के मार्ग पर बार-बार इस संसार में लौटकर आते रहते हैं।