PostgreSQL: CREATE DOMAIN to Abstract Data Type and Enforce Business Rules

PostgreSQL has one of the very good data type to store all the optional constraints and business rule of the application.

The DOMAIN is a type of DATA TYPE to abstract the common constraint and other business rule of Database system.

For example, we have several tables with Email column and we require to apply CHECK CONSTRAINT for validation of those Email columns.

For this kind of requirement, we can create one DOMAIN DATA TYPE, Instead of creating different CHECK CONSTRAINT for all the columns.
The DOMAIN DATA TYPE is also very easy to manage at one single place, without modifying each individual CHECK CONSTRAINT.

Below is a small demonstration of this:

CREATE one DOMAIN data type to validate simple email expression:
Doesn’t allow numbers in the domain name and doesn’t allow for top level domains that are less than 2 or more than 3 letters.

CREATE a Sample table using domain_email data type:

Insert record with valid email address:

Try to insert invalid email and test the CHECK CONSTRAINT of DOMAIN DATA TYPE:

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from

Leave a Reply

Be the First to Comment!

Notify of