Some RDBMSs support an enum type, which comprise a static, ordered set of values. Also known as enumerated types, enum types can be handy for making a column accept just a small set of values, such as days in the week, predefined clothing sizes, or any number of other preset values.

In PostgreSQL, if we want to create a table that uses an enum type, we need to create the enum type first, then apply it against the table. This is a bit different to other RDBMSs such as MySQL, where we don’t need to create the enum type first.

Example

Here’s an example of creating an enum type in PostgreSQL:

CREATE TYPE shirt_size AS ENUM ('small', 'medium', 'large');

This creates an enum type called shirt_size. It has three possible values. When a user enters data, it must be one of those values.

Now that we’ve created the enum type, we can go ahead and use it in a table:

CREATE TABLE shirts (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    size shirt_size
);

Here, the size column uses our shirt_size enum type. Any data that goes into that column must be one of the values specified in the enum type.

Testing the Enum Type

Let’s test out our enum value to see how it handles data:

INSERT INTO shirts (id, name, size) VALUES
(1, 'Red T-Shirt', 'small'),
(2, 'Blue Polo', 'medium'),
(3, 'Green Sweater', 'large'),
(4, 'Yellow Tank Top', 'small'),
(5, 'Black Hoodie', 'large');

Result:

INSERT 0 5

All five rows were inserted successfully. That’s because they all contained values that are listed in our shirt_size type.

Let’s test case-sensitivity:

INSERT INTO shirts (id, name, size) VALUES
(6, 'Purple Shirt', 'MEDIUM');

Result:

ERROR:  invalid input value for enum shirt_size: "MEDIUM"
LINE 2: (6, 'Purple Shirt', 'MEDIUM');
                            ^

PostgreSQL enums are case-sensitive and so this one failed.

Let’s try a NULL value:

INSERT INTO shirts (id, name, size) VALUES
(7, 'White Undershirt', NULL);

Result:

INSERT 0 1

This worked because I didn’t put any NOT NULL constraints on the column. If I’d put such a constraint on it, then it would’ve failed. It had nothing to do with the enum type. The enum type doesn’t require non-NULL values.

Let’s try an invalid value:

INSERT INTO shirts (id, name, size) 
VALUES (8, 'Orange Shirt', 'extra-large');

Result:

ERROR:  invalid input value for enum shirt_size: "extra-large"
LINE 2: VALUES (8, 'Orange Shirt', 'extra-large');
                                   ^

Failed, because that value isn’t in the list of values defined in our enum type.

Let’s test it with an empty string:

INSERT INTO shirts (id, name, size) VALUES
(9, 'Gray Vest', '');

Result:

ERROR:  invalid input value for enum shirt_size: ""
LINE 2: (9, 'Gray Vest', '');
                         ^

Failure.

Reviewing the Data

Now let’s take a look at everything that was inserted:

SELECT * FROM shirts;

Result:

 id |       name       |  size  
----+------------------+--------
  1 | Red T-Shirt      | small
  2 | Blue Polo        | medium
  3 | Green Sweater    | large
  4 | Yellow Tank Top  | small
  5 | Black Hoodie     | large
  7 | White Undershirt | 

Ordering of Enum Values

The ordering of the values in an enum type is the order in which the values were listed when the type was created. Ordering is not based on the string value itself. Therefore:

SELECT * FROM shirts
ORDER BY size ASC;

Result:

 id |       name       |  size  
----+------------------+--------
  1 | Red T-Shirt      | small
  4 | Yellow Tank Top  | small
  2 | Blue Polo        | medium
  3 | Green Sweater    | large
  5 | Black Hoodie     | large
  7 | White Undershirt | 

Key Points about PostgreSQL Enums

• In PostgreSQL, we must create the enum type separately using CREATE TYPE ... AS ENUM (...). This is different to some other RDBMSs, where we don’t need to create the type first.
• Use the created type name (in this case, shirt_size) as the column type.
• PostgreSQL enums are case-sensitive.
• Empty strings are not allowed for enum values in PostgreSQL.