Intro to Postgres enums
Tags: beginners, database, postgres, sql
Published: 2022-03-07 16:31:44

---
title: Intro to Postgres enums
published: true
description: Postgres database enum introduction
tags: beginner, database, postgres, sql
//cover_image: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4uc2c1exhl790erikku4.png
 
---

In most programming languages the use of enums is pretty common and heavily relied upon by developers. It took me quite some time to learn that this feature exists in the database realm as well. 
Therefore, I will provide a short introductory summary of enumerated types in Postgres.

Enums are used for representing a limited number of possible values. 
Status fields are a good example.
Let’s say we want to store account information inside a database table:

In our example an account can have one of the following status:

- ACTIVE
- DELETED
- BANNED

We can create a database enum by executing:

```sql
CREATE TYPE ACCOUNT_STATUS AS ENUM ( 'ACTIVE', 'DELETED', 'BANNED');
```

We can then use the generated enum in our table definition:

```sql
CREATE TABLE ACCOUNT (STATUS ACCOUNT_STATUS, USERNAME TEXT, EMAIL TEXT);
```

Now we insert a new account:

```sql
INSERT INTO ACCOUNT VALUES ('ACTIVE', 'testuser','user@test.com');
```

Since we set the datatype of the STATUS table to ACCOUNT_STATUS the database now takes care of validation.

If we try to insert an account with status  INACTIVE 

```sql
INSERT INTO ACCOUNT VALUES ('INACTIVE', 'testuser', 'user@test.com');
```

The database returns the following error:

```sql
ERROR: invalid input value for enum account_status: "INACTIVE"
```
Since "INACTIVE" is not valid for our defined enum.

Having a data validation mechanism like this on database level helps to enforce data integrity at all times.

## Querying enums

Unfortunately the values of enum types can **not** be retrieved by the following query:

```sql
SELECT * FROM ACCOUNT_STATUS
```

But one can make use of the built-in functions `UNNEST` and `ENUM_RANGE`

```sql
SELECT UNNEST(ENUM_RANGE(NULL::ACCOUNT_STATUS))
```

This query returns one row for each enum value. 
In our example:
- ACTIVE
- INACTIVE
- BANNED

## Alternatives to enums: check constraints

We can add a similar data validation mechanism to our database tables by adding a check constraint.
In our example the constraint would look like this:

```sql
CREATE TABLE ACCOUNT (STATUS TEXT CHECK (STATUS IN ('ACTIVE', 'INACTIVE', 'BANNED')), USERNAME TEXT, EMAIL TEXT);
```

After defining the column name and type we add the constraint by using the `CHECK` keyword. 
In the parenthesis following `CHECK` we define a boolean expression which must be true for every row of the table.
For better maintainability we can also give the check constraint a name:

```sql
CREATE TABLE ACCOUNT (STATUS TEXT CONSTRAINT ACCOUNT_STATUS_CONSTRAINT CHECK (STATUS IN ('ACTIVE', 'INACTIVE', 'BANNED')), USERNAME TEXT, EMAIL TEXT);
```

## Enum vs Check constraint


**Advantages of enums**

Once defined enums can be reused in different columns or tables.
Additionally, the usage of enums helps with understanding the database schema.
Because creation of a specific type conveys more purpose: e.g. column of type `ACCOUNT_STATUS` is more specific than a column of type `TEXT`.

**Disadvantages of enums**

With enums we defined a new data type for our column (note the `CREATE TYPE` syntax above).
Therefore, the built-in string operators and functions do **not** work on enums.
With a check constraint do not have to define our own data type, so any built-in operator and function for the used column data type can be used.



Like in other parts of software development, designing a database schema involves tradeoffs. 
So it is quite handy to have different tools in your tool belt!

If you like this summary, feel free to follow me on Twitter or here for more content.

Sources:
[Postgres docs](https://www.postgresql.org/docs/9.1/datatype-enum.html)