SQL and especially PostgreSQL provide a nice set of general purpose data types you can use to model your data. However, what if you want to store fewer generic data? What if you want to have more advanced server side check constraints? The way to do that in SQL and in PostgreSQL in particular is to use CREATE DOMAIN.

This blog will show you how to create useful new data types which are commonly needed in many applications.

CREATE DOMAIN: color codes

The first example is about color codes. As you can see not every string is a valid color code, so we got to add restrictions. Here are some examples of valid color codes: #00ccff, #039, ffffcc.

test=# \h CREATE DOMAIN
Command: CREATE DOMAIN
Description: define a new domain
Syntax:
CREATE DOMAIN name [ AS ] data_type
       [ COLLATE collation ]
       [ DEFAULT expression ]
       [ constraint [ ... ] ]

where constraint is:

       [ CONSTRAINT constraint_name ]
       { NOT NULL | NULL | CHECK (expression) }

URL: https://www.postgresql.org/docs/12/sql-createdomain.html

What CREATE DOMAIN really is, is a way to abstract a data type and to add constraints. The new domain can then be used just like all other data types (varchar, integer, boolean, etc). Let us take a look and see how it works:

CREATE DOMAIN color_code AS text
  CHECK (VALUE ~ '^#?([a-f]|[A-F]|[0-9]){3}(([a-f]|[A-F]|[0-9]){3})?$');

What we do here is to assign a regular expression to the color code. Every time we use a color code PostgreSQL will check the expression and throw an error in case the value does not match the constraint. Let is take a look at a real example:

test=# CREATE TABLE t_demo (c color_code);
CREATE TABLE

As you can see the domain is used as standard column type. Let us insert a value:

test=# INSERT INTO t_demo VALUES ('#04a');
INSERT 0 1

The value matches the constraint and therefore we are ok. However, if we try to add an incorrect input value PostgreSQL will complain instantly:

test=# INSERT INTO t_demo VALUES ('#04XX');
ERROR: value for domain color_code violates check constraint "color_code_check"

The CHECK constraint will prevent the insertion from happening.

Alphanumeric strings and passwords

More often than not you will need alphanumeric strings. Maybe you want to store an identifier, some voucher code or so. Alphanumeric strings are quite common and really useful. Here is how it works:

CREATE DOMAIN alphanumeric_string AS text
CHECK (VALUE ~ '[a-z0-9].*');

The regular expression is pretty simple in this case. We simply got to decide if we want to accept upper case or only lowercase letters. PostgreSQL offers case sensitive and case insensitive regular expression operators.

Imagine you want to check if a password is strong enough. A domain can help in this case as well:

-- password: Should have 1 lowercase letter, 1 uppercase letter, 1 number,
-- 1 special character and be at least 8 characters long
CREATE DOMAIN password_text AS text
CHECK (VALUE ~ '(?=(.*[0-9]))(?=.*[\[email protected]#$%^&*()\\[\]{}\-_+=~`|:;"''<>,./?])(?=.*[a-z])(?=(.*[A-Z]))(?=(.*)).{8,}');

This expression is a bit more complicated but there is no need to understand it. Just copy and paste it and you will be fine. Also: This expression is here to verify data – it is not an invitation to store plain text passwords in the database.

CREATE DOMAIN for the web

If you want to store URLs and if you want to make sure that the format is correct you can also make use of CREATE DOMAIN. The following snippet shows how you can verify an URL:

CREATE DOMAIN url AS text
CHECK (VALUE ~ 'https?:\/\/(www\.)?[[email protected]:%._\+~#=]{2,256}\.[a-z]{2,6}\b([[email protected]:%_\+.~#()?&//=]*)');

COMMENT ON DOMAIN url IS 'match URLs (http or https)';

If you want to match a domain name only the following expression will do:

-- domains
CREATE DOMAIN domain AS text
CHECK (VALUE ~ '^([a-z][a-z0-9-]+(\.|-*\.))+[a-z]{2,6}$');

COMMENT ON DOMAIN domain IS 'match a domain name. www in front is not allowed';

One word about database performance

People often ask if a domain has performance implications. Basically all the domain does is to enforce a constraint – the underlying data type is still the same and therefore there is not much of a difference between adding a CHECK constraint for every column. The real benefit of a domain is not better performance – it is data type abstraction. Abstraction is what you would do in any high-level language.

Finally …

CREATE DOMAIN is not the only cool feature in PostgreSQL. If you want to know more about regular expressions in PostgreSQL I suggest checking out my blog post about how to match “Gadaffi” which is more complicated than it might look at first glance.