Use PostgreSQL® DOMAIN rules to validate columns of data
Tags: postgres, validation, data, domain
Published: 2023-05-05 07:00:00

---
title: Use PostgreSQL® DOMAIN rules to validate columns of data
published: true
date: 2023-05-05 07:00:00 UTC
tags: postgresql, validation, data, domain
canonical_url: https://aiven.io/developer/use-postgresql-domain-rules-to-validate
---

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](https://www.postgresql.org/docs/current/sql-createdomain.html), 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](#pay-attention-when-altering) and [dropping](#pay-attention-when-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](https://console.aiven.io/signup) if you don't have one already and [install the Aiven CLI](https://docs.aiven.io/docs/tools/cli#install). 

You'll need to [authenticate to the CLI](https://docs.aiven.io/docs/tools/cli#authenticate) before you can create your first service and once authenticated, you can [create a service](https://docs.aiven.io/docs/tools/cli/service#avn-service-create):

```
avn service create demo-pg-italian-addresses  \
    --service-type pg                         \
    --plan hobbyist                           \
    --cloud google-europe-west3
```

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:

```
avn service wait demo-pg-italian-addresses
```

And connect to it (this runs [psql](https://www.postgresql.org/docs/current/app-psql.html) for you, so that needs to be installed) with:

```
avn service cli demo-pg-italian-addresses
```

## 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](https://www.postgresql.org/docs/current/sql-createdomain.html) to create a domain for these three values, `ITALIAN_ADDRESS`.

```
CREATE DOMAIN ITALIAN_ADDRESS
    TEXT
    NOT NULL
    CONSTRAINT ITALIAN_ADDRESS_CHECK CHECK(UPPER(VALUE) ~ '^(VIA|VIALE|PIAZZA) [A-Z \-]+ [0-9]+[A-Z]*$');
```

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](https://www.postgresql.org/docs/current/functions-matching.html), 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:

```
CREATE TABLE ADDRESSES(
    ID SERIAL,
    NAME TEXT,
    ADDRESS ITALIAN_ADDRESS
);
```

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:

```
INSERT INTO ADDRESSES(NAME, ADDRESS) VALUES ('UGA', 'PIAZZA DEL CORSO 8A');
```

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`?

```
INSERT INTO ADDRESSES(NAME, ADDRESS) VALUES ('UGO', 'STRADA NASCOSTA 5');
```

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

```
ERROR:  value for domain italian_address violates check constraint "italian_address_check"
```

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:

```
ALTER DOMAIN ITALIAN_ADDRESS SET DEFAULT 'UNKNOWN ADDRESS';
```

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

```
INSERT INTO ADDRESSES(NAME) VALUES ('LUIGI');
```

Instead we get an error:

```
ERROR:  value for domain italian_address violates check constraint "italian_address_check"
```

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`:

```
ALTER DOMAIN ITALIAN_ADDRESS SET DEFAULT 'VIA UNKNOWN ADDRESS 1';
```

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

```
INSERT INTO ADDRESSES(NAME) VALUES ('LUIGI');
```

Checking the data in the `ADDRESSES` table with: 

```
SELECT * FROM ADDRESSES;
```

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

```
 id | name  |        address
----+-------+-----------------------
  1 | UGA   | PIAZZA DEL CORSO 8A
  4 | LUIGI | VIA UNKNOWN ADDRESS 1
(2 rows)
```

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:

```
ALTER DOMAIN ITALIAN_ADDRESS
    DROP CONSTRAINT ITALIAN_ADDRESS_CHECK;
```

Then add the new check, changing `PIAZZA` to `PIZZA`:

```
ALTER DOMAIN ITALIAN_ADDRESS
    ADD CONSTRAINT ITALIAN_ADDRESS_NEW_CHECK CHECK(UPPER(VALUE) ~ '^(VIA|VIALE|PIZZA) [A-Z \-]+ [0-9]+[A-Z]*$');
```

But the command above generates an error:

```
ERROR:  column "address" of table "addresses" contains values that violate the new constraint
```

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

```
SELECT *
from ADDRESSES
where NOT ADDRESS ~ '^(VIA|VIALE|PIZZA) [A-Z \-]+ [0-9]+[A-Z]*$';
```

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

```
 id | name |       address
----+------+---------------------
  1 | UGA  | PIAZZA DEL CORSO 8A
(1 row)
```

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:

```
ALTER DOMAIN ITALIAN_ADDRESS
    ADD CONSTRAINT ITALIAN_ADDRESS_NEW_CHECK CHECK(UPPER(VALUE) ~ '^(VIA|VIALE|PIZZA) [A-Z \-]+ [0-9]+[A-Z]*$')  NOT VALID;
```

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

```
ALTER DOMAIN ITALIAN_ADDRESS
    VALIDATE CONSTRAINT ITALIAN_ADDRESS_NEW_CHECK;
```

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

```
ERROR:  column "address" of table "addresses" contains values that violate the new constraint
```

We need to update the data in the table:

```
UPDATE ADDRESSES SET ADDRESS=REPLACE(ADDRESS, 'PIAZZA', 'PIZZA') WHERE ADDRESS LIKE 'PIAZZA%';
```

Then check the data with `SELECT * FROM ADDRESSES;`

```
 id | name  |        address
----+-------+-----------------------
  1 | UGA   | PIZZA DEL CORSO 8A
  4 | LUIGI | VIA UNKNOWN ADDRESS 1
(2 rows)
```

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

```
ALTER DOMAIN ITALIAN_ADDRESS
    VALIDATE CONSTRAINT ITALIAN_ADDRESS_NEW_CHECK;
```

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

```
INSERT INTO ADDRESSES (NAME, ADDRESS) VALUES ('CARLA', 'PIAZZA MAGGIORE 33');
```

The above SQL throws the error:

```
ERROR:  value for domain italian_street violates check constraint "italian_street_new_check"
```

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?

```
DROP DOMAIN ITALIAN_ADDRESS;
```

We get the following error, signaling that there are dependencies

```
ERROR:  cannot drop type italian_address because other objects depend on it
DETAIL:  column address of table addresses depends on type italian_address
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
```

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

```
DROP DOMAIN ITALIAN_ADDRESS CASCADE;
```

Check the table:

```
SELECT * FROM ADDRESSES;
```

No `ADDRESS` column anymore!

```
 id | name
----+-------
  1 | UGA
  4 | LUIGI
(2 rows)
```

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 centralise the definition and checks.

Check out some additional resources on the topic:

* [PostgreSQL create domain documentation](https://www.postgresql.org/docs/current/sql-createdomain.html)
* [PostgreSQL alter domain documentation](https://www.postgresql.org/docs/current/sql-alterdomain.html)
* [PostgreSQL regular expressions documentation](https://www.postgresql.org/docs/current/functions-matching.html)
* [Aiven for PostgreSQL](https://aiven.io/postgresql)
* [Aiven for PostgreSQL documentation](https://docs.aiven.io/docs/products/postgresql)