enum is PostgreSQL

In this article, you are going to understand the PostgreSQL enum and try to get the answer below question,

What does an enum?
why use an enum?
How to use an enum?

Introduction:

PostgreSQL supports the boolean type of binary data type, where you can add values such as yes or no, which is like binary data type because the value of this field is either of one. For example isActive column where you will have either true or false binary values.

If you have more than two values for particular data then you are using the Enum type which is very similar to Java Enum. PostgreSQL support another type i.e. Enum type. Let us take an example type of cricket play format such T-20, 1-Day, and Test Matches. This kind of data type also has to be strict to prevent other values from being written. Then enum comes to the rescue.

What does an enum:

An enum type is a top solution to represent the discrete set of values with strict types. Another advantage is that it represents the same analogy that you have in most of the OOP languages such as Java.

It represents the state of the data very well. Let us take an example of the money transfer application where you have multiple states of the transaction, it can be INITIATED, INPROGRESS, WAITING_APPROAVL, APPROVED, WAITBANKCONFIRMATION, and TRANSFERRED. Enum can handle this efficiently.

There may be more or fewer states based on organization and context.

PostgreSQL enum has certain behaviors.

It is treated as a type in PostgreSQL.
It applies to all tables in the same schema.
It has static values.
The enum value is case-sensitive.
The enum value size is 4 bytes.
Each won't allow duplicate values.
Adding enum value requires ALTER operation.
Order of enum values matters when querying.

Enum usage:

Now you know what is enum, what are the benefits of using enum. Now let us see when to use an enum. Use enum in the below situation:

If you have unique values.
Enum values have different priorities.
Cheap constraint is preferable.
Won't expect frequent changes.
Not too many values, preferable not in double-digits.

Make hands dirty/ Practical guide:

Setup Docker and Postgres in local click here.

Let us make hands dirty and create an enum by running the below query.

Create Enum:

CREATE TYPE product_type AS ENUM
    ('electronics', 'cosmetic', 'edible');

Rename Enum Type:

ALTER TYPE product_type RENAME TO product_type_offline;

Retrieve Enum values:

SELECT UNNEST(enum_range(null::product_type_offline)) 
AS product_type;

OR 

SELECT unnest(enum_range(NULL::product_type_offline))::text

OR 

SELECT enum_range(NULL::product_type_offline)

Add new value to enum:

As your project grows, you want to add a new value to an enum, you can add using the below query.

ALTER TYPE product_type ADD VALUE 'OTHERS';

OR
ALTER TYPE product_type ADD VALUE 'OTHERS' BEFORE 'electronics';

OR 

ALTER TYPE product_type ADD VALUE 'OTHERS' AFTER 'cosmetic';

Change OR delete enum value:

ALTER TYPE product_type  RENAME VALUE 'cosmetic' TO 'beauty_products';

Delete or Drop Enum type:

DROP TYPE product_type ;

Conclusion:

An enum type is a useful feature for data safety and assured the set of values. This article is helpful to remind you of the PostgreSQL enum and Java Enum and how, when, and where to use the enum.

Understand PostgreSQL Enum type!

Comments

More from this blog

Adopting an MCP-First API Design for the Agentic Era | Beyond REST.

Article 6: Mastering Kestra's Plugin Ecosystem.

Article 5: Advanced Workflow Patterns in Kestra.

Article 4: Building Your First ETL Pipeline with Kestra.

Article 3: Understanding Kestra's Architecture: Flows, Tasks, and Namespaces

Introduction:

What does an enum:

Enum usage:

Make hands dirty/ Practical guide:

Create Enum:

Rename Enum Type:

Retrieve Enum values:

Add new value to enum:

Change OR delete enum value:

Delete or Drop Enum type:

Conclusion:

More such articles:

Command Palette

Comments

More from this blog

Introduction:

What does an enum:

Enum usage:

Make hands dirty/ Practical guide:

Create Enum:

Rename Enum Type:

Retrieve Enum values:

Add new value to enum:

Change OR delete enum value:

Delete or Drop Enum type:

Conclusion:

More such articles: