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:

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of