PostgreSQL Domain Types and Enums: Ensuring Data Integrity

4 min readJan 6, 2025

Welcome back to the third post in our series on advanced PostgreSQL features. In the previous posts, we explored how to design a database schema and enforce data integrity using constraints. Building on that foundation, today we’ll dive into two powerful PostgreSQL tools: domain types and enums.

When working with databases, ensuring data integrity is critical. One of the ways to achieve this in PostgreSQL is by using domain types and enums. These help to define constraints and restrict the type of data that can be inserted into a table. Let’s dive into how they work and why they are so useful.

Custom Domain Types

A domain type in PostgreSQL is essentially a custom data type that adds constraints to an existing type. This approach allows you to reuse constraints across multiple tables, making your database schema cleaner and more consistent.

In simple terms, a domain type is a combination of a data type and a constraint. While you could add constraints directly to each column, using a domain type allows you to enforce the same rules across several tables without repeating yourself.

Here’s an example of how we can create and use a custom domain type for a US Postal Code:

CREATE DOMAIN us_postal_code AS TEXT 
CONSTRAINT postal_code_format CHECK (
    VALUE ~ '^\d{5}$' OR VALUE ~ '^\d{5}-\d{4}$'
);

In the above code:

We define a domain called us_postal_code as a TEXT type.
The constraint ensures the postal code follows the format of either a 5-digit code (e.g., 12345) or a 9-digit code with a hyphen (e.g., 12345-6789).

Now, let’s create a table and use this domain type:

CREATE TABLE addresses (
    city TEXT,
    address TEXT,
    post_code us_postal_code
);

Here are a few insert operations:

INSERT INTO addresses (city, address, post_code)
VALUES ('LA', 'Main-Street', '12345');
-- SUCCESS
INSERT INTO addresses (city, address, post_code)
VALUES ('LA', 'Main-Street', '1234');
-- ERROR: value for domain us_postal_code violates check constraint "postal_code_format"

The advantage of using a domain type is that you can reuse this us_postal_code domain across multiple tables, ensuring consistency without redefining the constraints each time.

Enums in PostgreSQL

An ENUM is a finite set of predefined values. Enums are an excellent way to store a field that has a fixed set of possible values. For instance, if you were managing the status of an order in an e-commerce system, you might want to define a set of possible statuses, such as {NEW, PAID, IN_DELIVERY, DELIVERED, CANCELED}.

Benefits of Enums:

Text Representation for Humans: Enums are stored as integers in the database, but they are represented as text to humans, which is more intuitive.
Fixed Values: Enums are great when you know that the set of values is fixed and doesn’t change frequently.

Here’s how you can create an enum in PostgreSQL:

CREATE TYPE mood AS ENUM ('HAPPY', 'SAD', 'NEUTRAL');

Now, you can use the enum type in a table:

CREATE TABLE clients (
    name TEXT,
    current_mood MOOD
);

Inserting data into the table is easy:

INSERT INTO clients(name, current_mood)
VALUES ('Bruce', 'HAPPY'),
       ('Ann', 'SAD'),
       ('Bob', 'NEUTRAL');

If you try to insert a value that doesn’t exist in the enum, you’ll get an error:

INSERT INTO clients(name, current_mood)
VALUES ('Jack', 'Obsessed');
-- ERROR: invalid input value for enum mood: "Obsessed"

Enum Ordering

One interesting feature of enums is their ordering. The order of enum values is the order in which they are defined. This order is crucial when performing queries like ORDER BY:

SELECT * FROM clients ORDER BY current_mood;

-- OUTPUT
+-----+------------+
|name |current_mood|
+-----+------------+
|Bruce|HAPPY       |
|Ann  |SAD         |
|Bob  |NEUTRAL     |
+-----+------------+

The result will be sorted based on the internal order of the enum values (HAPPY = 1, SAD = 2, NEUTRAL = 3).

Adding New Enum Values

You can extend an existing enum type by adding new values. The new values are added to the end of the list by default, but you can also specify where you want them inserted:

ALTER TYPE mood ADD VALUE 'EXCITED';

INSERT INTO clients(name, current_mood)
VALUES ('Jamie', 'EXCITED');

SELECT distinct * FROM clients ORDER BY current_mood;

+-----+------------+
|name |current_mood|
+-----+------------+
|Bruce|HAPPY       |
|Ann  |SAD         |
|Bob  |NEUTRAL     |
|Jamie|EXCITED     |
+-----+------------+

If you want to control the order of the values, you can specify whether the new value should come before or after existing ones:

ALTER TYPE mood ADD VALUE 'OBSESSED' BEFORE 'SAD';
ALTER TYPE mood ADD VALUE 'AFRAID' AFTER 'HAPPY';

Additional PostgreSQL Functions

A useful function for developers working with PostgreSQL is PG_TYPEOF(), which returns the internal data type of a given value. This can be helpful for debugging or understanding data conversions in your queries.

PG_TYPEOF('11:13:59.77091+05'::time);  -- Returns: time without time zone
PG_TYPEOF(12.2999);  -- Returns: numeric

Conclusion

By using custom domain types and enums in PostgreSQL, you can enhance data integrity and improve the maintainability of your database schema. Domain types allow you to enforce constraints and reuse them across multiple tables, while enums help manage fixed sets of values in a human-readable format.

If you find this post useful, like and subscribe to stay tuned for future posts. Happy Coding!