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.
Table of Contents
This blog will show you how to create useful new data types with constraints for:
These are all commonly needed in many applications.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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:
|
1 2 |
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:
|
1 2 |
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:
|
1 2 |
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:
|
1 2 |
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.
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:
|
1 2 |
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:
|
1 2 3 4 5 |
-- 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.
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:
|
1 2 |
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@:%_+.~#()?&//=]*)? |
If you want to match a domain name only, the following expression will do:
|
1 2 3 |
-- domains CREATE DOMAIN domain AS text CHECK (VALUE ~ '^([a-z][a-z0-9-]+(.|-*.))+[a-z]{2,6} |
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.
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.
You need to load content from reCAPTCHA to submit the form. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
Yes, that regular expression was pretty broken. The actual error was caused by
{2,256}, because the repetition count cannot exceed 255 in PostgreSQL. I have tried to fix the code, but don't take it as the authoritative regular expression for an URL. This is only an example for the usefulness of domains.