Use PostgreSQL® DOMAIN rules to validate columns of data

When dealing with data inputs, there's always one or more fields that need strict validation: a T-shirt size can accept only a few precise values, a shoe size should be within a range of numbers, a phone number should follow a precise pattern. It's good to perform these checks at the application level, for example as a part of a form input, but for data consistency it's best to validate that data when adding it to the database as well.

PostgreSQL® allows these checks to be performed in several ways, like using table constraints. In this article, we'll talk about the DOMAIN feature, which defines column validation centrally for use across multiple database tables. We'll showcase the power and the limits of the DOMAIN approach, using some fairly basic examples of verifying Italian addresses in a table.

Centralized vs per-table approach

As mentioned before, the DOMAIN option allows defining the constraints of a column type once and reusing it across several tables. The benefit of a unique definition are evident: there's one truth and one point of contact. Needing to define the same constraints across several tables could result in human error or misalignment during constraint evolution.

On the other side, having a unique definition can be tricky in cases when different tables need to evolve such a definition in different ways or across different times. In such cases, having a unique point of contact can become dangerous. Check the examples below regarding altering and dropping DOMAIN definitions for more info.

Pick a database

For our test case, we can use any kind of PostgreSQL, it being on-premises, Docker, or cloud, since the DOMAIN function is native to PostgreSQL and has been available for a long time. For the purpose of the blog we'll use Aiven for PostgreSQL®. Sign up for an Aiven account if you don't have one already and install the Aiven CLI.

You'll need to authenticate to the CLI before you can create your first service and once authenticated, you can create a service:

Loading code...

The above call creates an Aiven for PostgreSQL service (--service-type pg) named demo-pg-italian-addresses, using the smallest hobbyist plan on the google-europe-west3 region.
We can wait for the service to be up and running with:

Loading code...

And connect to it (this runs psql for you, so that needs to be installed) with:

Loading code...

Define the allowed Italian street values

For the purposes of this demonstration, let's say Italian streets begin with three indicators: Via, Viale, and Piazza. In reality there are more ways to indicate streets in Italy, but let's keep it simple for now.

Let's use the DOMAIN feature to create a domain for these three values, ITALIAN_ADDRESS.

Loading code...

If we check the details of the above command we can see:

• The name is ITALIAN_ADDRESS
• The data type is TEXT
• There is a NOT NULL constraint forcing the column to never be empty
• There is a CONSTRAINT named ITALIAN_ADDRESS_CHECK. This performs a check, based on a regular expression, ensuring the address:
  • Starts with one of VIA, VIALE or PIAZZA identifying the type of street/square
  • Is followed by a space and a string of A-Z, space or - characters identifying the street/square name
  • Is followed by a space one or more numbers (+) identifying the building number
  • Is followed by zero or more letters (*) identifying the block within the building

Use the domain within a table definition

Now that we've defined the domain, we can use it when creating a table. Let's create the table ADDRESSES to host our customer information, such as the customer's name and their address:

Loading code...

In the above SQL, the ADDRESS column refers to the ITALIAN_ADDRESS domain. Now we can insert rows, checking that the domain rules are obeyed:

Loading code...

The above insert is successful, since it follows the domain definition. But what happens if we try something different, like an address starting with STRADA?

Loading code...

We get an error, since the domain check is failing:

Loading code...

Our domain is working as expected and we can now use it in other table definitions as well. For example if we have a SHOPS table that needs to contain a validated address, we can define it as SHOPS ITALIAN_ADDRESS. This is the beauty of the DOMAIN feature: one central definition, multiple usages.

Set a default value

The domain definition prohibits null values in the column, but we might want to allow an insert that skips the column definition by setting a domain default value:

Loading code...

Now, if we try to add a new customer without any associated address, we might expect a successful insert:

Loading code...

Instead we get an error:

Loading code...

The error is due to the fact that our default value UNKNOWN ADDRESS is not compliant with the constraint defined in the domain (it doesn't start with VIA, VIALE or PIAZZA). If we switch the default to a compatible value like VIA UNKNOWN ADDRESS 1:

Loading code...

We can successfully push the customer information without the address now:

Loading code...

Checking the data in the ADDRESSES table with:

Loading code...

Will show the row for LUIGI with the default address set.

Loading code...

Note that there is a hierarchy for default values: Data type default -> Domain default -> Table column default.

The data type default value can be overridden by the domain default and the domain default can be overridden by the table column default.

Pay attention when altering domains

Changing domains already in use can be tricky. For example, let's say that tomorrow italy decides that all PIAZZA addresses should be renamed to PIZZA, so we need to update our domain to reflect this. First we remove the old constraint:

Loading code...

Then add the new check, changing PIAZZA to PIZZA:

Loading code...

But the command above generates an error:

Loading code...

Why can't we enable the new constraint? Let's have a look at the data that doesn't satisfy the new constraint, with:

Loading code...

The WHERE clause in that query checks the address against the same regular expression as defined in the domain. The output is:

Loading code...

We can see that the PIAZZA DEL CORSO 8A row doesn't satisfy our new constraint (it should start with PIZZA). Let's define the constraint as NOT VALID for now:

Loading code...

The NOT VALID clause, allows us to add the constraint but not validate the rows in the table. If we check the validity:

Loading code...

We still get the same error since we didn't update the data:

Loading code...

We need to update the data in the table:

Loading code...

Then check the data with SELECT * FROM ADDRESSES;

Loading code...

The UGA row should now be in line with the new constraint, so we can validate it:

Loading code...

Success! Now the check is enabled, therefore trying to insert a PIAZZA address is prohibited.

Loading code...

The above SQL throws the error:

Loading code...

Besides this example, another catch with the ALTER DOMAIN command is that any expression or function we add as part of the domain creation should be immutable. If we have a user-defined function as part of the CREATE DOMAIN expression, and we're able to modify the expression/function after domain creation, we'll get an error when reloading the data in that database.

Pay attention when dropping

What happens when dropping a domain used in one or more tables?

Loading code...

We get the following error, signaling that there are dependencies

Loading code...

If we really want to drop it we can use the CASCADE option:

Loading code...

Check the table:

Loading code...

No ADDRESS column anymore!

Loading code...

It's difficult to drop an in-use domain without affecting the rest of the database tables. The beauty of centrally managed column definition is playing against us in this scenario. Since the definition is unique, any table using it will have columns disappear if the related definition gets deleted.

Conclusion

PostgreSQL domains are a powerful feature, allowing a central definition of column validation rules that can be then applied across several tables. The evolution of the definition can be tricky, but for standard, non-evolving columns it can represent a good way to centralize the definition and checks.

Check out some additional resources on the topic:

• PostgreSQL create domain documentation
• PostgreSQL alter domain documentation
• PostgreSQL regular expressions documentation
• Aiven for PostgreSQL®
• Aiven for PostgreSQL® documentation