Domains
Storing data in proper formats ensures data integrity, prevents errors, optimizes performance, and maintains consistency across systems by enforcing validation rules and enabling efficient data management. For these reasons, top tier relational databases like PostgreSQL offer a variety of data types. In addition, PostgreSQL enables custom data type creation via the "CREATE DOMAIN" and "CREATE TYPE" statements, allowing developers to extend data types for enhanced application-specific data validation, integrity, and consistency. In today's blog, we'll learn how to create a custom Domain for the free DVD Rental database using Navicat Premium 17. Part 2 will cover Types.
A Quick Comparison of CREATE DOMAIN and CREATE TYPE
While both the CREATE DOMAIN and CREATE TYPE statements may be employed to create user-defined data types, there are some key differences to be aware of:
- CREATE DOMAIN creates a user-defined data type with constraints such as NOT NULL, CHECK, etc.
- CREATE TYPE creates a composite type used in stored procedures as the data types of returned values.
Creating An Email Domain
Domains centralize constraint management by allowing you to define reusable validation rules across multiple tables, such as creating a standard constraint that prevents NULL values and trims whitespace for specific field types. Here's an example that creates a domain for email addresses with a validation check:
CREATE DOMAIN email AS VARCHAR(255)
CHECK (
VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$'
);
-- Use in a table definition
CREATE TABLE customer_contacts (
customer_id INT,
contact_email email
);
Navicat Premium 17 and Navicat for PostgreSQL 17 both offer a GUI-based tools for generating domains and types without having to know all of the exact syntax. You'll find both under "Others" in the main toolbar. (Both menu items are highlighted in red below):
The Domain tool includes four tabs: General, Checks, Comment, and SQL Preview.
General Attributes
All domains are based on an underlying type. In this case, it's VARCHAR. Once we select an Underlying Type Category of "Base Type", we can select "pg_catalog" and "varchar" from the two Underlying Type drop-downs. We'll also need to make sure that our VARCHAR has a Length of 255. Here is the General Tab with all of that information provided:
Checks
On the next tab, we can define one or more checks to perform when someone attempts to assign a value to our type. Our check will test the value against a RegEx (regular expression):
SQL Preview
At this point we can either proceed to Save the Domain, which will execute the generated CREATE DOMAIN statement, or we can click on the SQL Preview tab to view the statement before saving:
Notice that the Domain name is "Untitled" since we haven't yet saved the definition. That is normal.
Using the email Domain In a Table
The best way to confirm that our "email" Domain was created is to try it in a table. The "staff" table in the "dvdrental" database includes an email field. Currently, it's storing values as a VARCHAR without any validation checks. We can change the type to our Domain by selecting the "(Domain)" option from the Type drop-down in the Table Designer and then choosing "public" and "email" for the Object Type:
Once we save the table, attempting to change (or add) a value which is not a valid email address will result in a constraint violation:
Conclusion
By creating a custom Domain for the free dvdrental database, we saw how domains help centralize constraint management by allowing us to define reusable validation rules. In part 2, we'll create our own type using Navicat Premium 17's Type tool.
