Hello! Enums are very familiar concept for software developer, especially, C#, java and C++ developers. In this article we will discuss about how to create enum in PostgreSQL.
What is an enum?
PostgreSQL enum is a short format of enumerations, a data type of static set of values. Software developers use enums to save static set of values. Let me explain with and example.
You are developing a software, or designing a database in our case, which saves day of the week. You have several options to save day of the week:
- Let enduser to write, like Sunday, but nobody can guarantee, all users will use the same standard, values may vary like SUNDAY, sunday, Sunday etc, you need a piece of code to handle this exceptions.
- You can save as numeric values like 1,2,3,4…. After you leave that role, next person replacing you will spend time to understand.
- Create an enum with values, so users only can save with the values you set.
Use Cases for PostgreSQL enum
In general, if you can think a field as a select box you can use that column as enums. Let’s think about storing name of a city. (yes, in most cases, storing an id is a better option) End users or developers might use different values, such as:
- Istanbul
- İstanbul
- istanbul
- ıstanbul
- ISTANBUL
- İSTANBUL
I’ve faces this issue many time in my career while working with banks and insurance companies. Are they same? No, situation depends on database’s collation, but no, they are not the same.
A Quick Solution
Most of the object oriented programming languages support enums. Using same enums to store static set of values is a quick solution.
For instance if you are developing a mood tracker application, you can use same set of values in your Java backend and PostgreSQL database. (Special thanks to PostgreSQL Documentation about Enums for this example.)
PHP, Rust, Java, C#, Typescript… Many languages support enums.
How to Create enum in PostgreSQL?
An enum is considered as TYPE in PostgreSQL.
To create an enum in PostgreSQL we use CREATE TYPE verb in PostgreSQL as follows:
CREATE TYPE daysOfWeek AS ENUM ('monday', 'tuesday', 'wednesday', 'thursday', 'friday', 'saturday');
ALTER TYPE daysOfWeek ADD VALUE 'sunday' AFTER 'saturday';
Create Type statement defines an enum, but on purpose i didn’t add sunday to show you alter statement as well. You can see an ADD VALUE here even though, removing an item is not possible.
To validate my work, i asked dbeaver to create create statement of this enum:
Limitations of Enums in PostgreSQL
Enums are case sensitive, SUNDAY and sunday is not same.
You delete an enum completely but you can not remove an item from the set. Also changing order of values is not possible.
pg_enum catalog stores the enum data, you can directly query from that catalog.
Summary
You can use enums to save static set of values in PostgreSQL. This features couples well with object oriented programming languages.
Please leave comment below if you appreciate my work.
If you have question to ask, please do no hesitate to ask me!
See you!
Hi! I’m an IT Specialist
I want to hear from you! I am Working with enterprises for 10+ years to improve their infrastructure and efficiency.
Get in touch with me.
Leave a Reply