Jan 17, 2025 by Robert Gravelle

Enumerated Types

In this series on creating custom data types in PostgreSQL using Navicat Premium 17 we've explored a couple of options so far. In part 1, we learned how to create a custom Domain for the free DVD Rental database. Last week, we created a Composite Type to return complex data from a user-defined function. Today's blog will cover Enumerated Types, which limit values a set of predefined options.

A Quick Overview of the ENUM TYPE

Enumerated types (ENUMs) allow us to define a data type with a static, ordered set of values. This is useful for situations where a column must contain one of a limited set of predefined values.

Like other PostgreSQL types, the ENUM type is created using the CREATE TYPE statement. Here's an ENUM that defines four user statuses:

CREATE TYPE user_status AS ENUM ('active', 'inactive', 'suspended', 'pending');

Here's another that defines movie ratings:

CREATE TYPE movie_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');

Once defined, we can use our custom type in a table as follows:

CREATE TABLE films (
  film_id SERIAL PRIMARY KEY,
  title VARCHAR(255),
  rating movie_rating
);

Creating an Enumerated Type in Navicat 17

An easier way to define an Enumerated Type is to use Navicat's GUI-based tools. You'll find them in both Navicat Premium 17 and Navicat for PostgreSQL 17. To access the Type tool, simply click "Others" in the main toolbar and then select "Type" from the drop-down:

That will bring up the Objects pane, where we'll see a list of existing types. To create a new one, click on the arrow next to the "New Type" item in the "Objects" toolbar and select the "Enum" item from the context menu:

That will launch the Type designer in a new tab. On the General tab there will be an empty cell in which we can enter the first Label for our Enum, i.e., "G":

We can add a new row to enter the next Label by clicking on "Add Label". Once all the Labels have been entered, the General tab should look like this:

Before clicking the "Save" button we can take a look at the statement that Navicat will generate by clicking on the "SQL Preview" tab:

Notice that the Type name is "Untitled" since we haven't yet saved the definition. That is expected.

Upon clicking on the "Save" button, we are presented with a "Save As" dialog where we can give our Type a name. Let's call it "film_rating":

Using the film_rating Type In a Table Definition

Now we can use the "film_rating" type just like any other PostgreSQL data type. For instance, we can set a table column to our custom type. We can even change the type on an existing table provided that its data values conform to our Enum value. In fact, changing a column's type from a generic VARCHAR to the stricter ENUM is an efficient way to quickly determine if a column contains invalid values.

If we open the "film" table in the Navicat Table Designer, we can set the "rating" column to our "film_rating" type by selecting "(Type)" from the "Type" drop-down and then setting the "Object Type" to "film_rating":

Also make sure that the "Collation" field is blank.

If the column doesn't contain any invalid values, we should be able to Save the table definition without any errors or warnings.

One of the advantages to setting a column type to an ENUM is that Navicat will provide a drop-down for choosing a value:

Conclusion

In today's blog, we created an Enumerated Type using Navicat Premium 17's Type tool and updated an existing table to utilize our custom type in order to constrain column values. Part 4 will proceed with the Range Type.