PostgreSQL: Useful new data types – CREATE DOMAIN

UPDATED 08.05.2023 – 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 with constraints for:

• color codes
• alphanumeric strings and passwords
• storing URL’s

These are all 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 you must 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/15/sql-createdomain.html

What CREATE DOMAIN really does is 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’s 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 you do here is to assign a regular expression to the color code. Every time you use a color code, PostgreSQL will check the expression and throw an error in case the value does not match the constraint. Let’s take a look at a real example:

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

You can see that the domain used is a standard column type. Let’s insert a value:

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

The value matches the constraint, therefore everything is OK. However, if you 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’ll need alphanumeric strings. Maybe you want to store an identifier, or a voucher code. Alphanumeric strings are quite common and really useful. Here’s how it works:

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

The regular expression is pretty simple in this case. You need to decide if you want to accept upper-case or only lower-case 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]))(?=.*[\!@#$%^&*()\\[\]{}\-_+=~`|:;"''<>,./?])(?=.*[a-z])(?=(.*[A-Z]))(?=(.*)).{8,}');

This expression is a bit more complicated, but there’s no need to understand it. Just copy and paste it and you’ll 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?:\/\/[-a-zA-Z0-9@:%._\+~#=]{2,255}\.[a-z]{2,6}(\/[-a-zA-Z0-9@:%._\+~#=]*)*(\?[-a-zA-Z0-9@:%_\+.~#()?&//=]*)?$');

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’s not much of a difference between adding a CHECK constraint for every column. The real benefit of a domain is not better performance – it’s data type abstraction. Abstraction is what you would do in any high-level language.

Finally …

CREATE DOMAIN isn’t 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.

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.