Do you need the same column with the same check constraint twice? Create a domain!

Did you know that you can create domains in PostgreSQL? No, nothing to worry about. We’ll take Frank’s leave for a new opportunity as a chance to introduce the concept of domains. @Franck: Yes, although we all fully understand your decision and the reasons to move on to a new challenge, this post is dedicated to you and you need to be the example in the following little demo. Lets go …

For the (not in any way serious scope) of this post lets assume that we do not want Franck anymore to blog on our blog site. We want to ban him. Of course we could simply delete his user account or disable the login. But, hey, we want to do that by using a domain as that is much more fun to do. Lets assume our blog software comes with two little tables that look like this:

postgres=# d blogs
                            Table "public.blogs"
 Column |  Type   | Collation | Nullable |              Default              
--------+---------+-----------+----------+-----------------------------------
 id     | integer |           | not null | nextval('blogs_id_seq'::regclass)
 author | text    |           |          | 
 blog   | text    |           |          | 
Indexes:
    "blogs_pk" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "blog_comments" CONSTRAINT "comments_ref_blogs" FOREIGN KEY (blog_id) REFERENCES blogs(id)

postgres=# d blog_comments
                             Table "public.blog_comments"
 Column  |  Type   | Collation | Nullable |                  Default                  
---------+---------+-----------+----------+-------------------------------------------
 id      | integer |           | not null | nextval('blog_comments_id_seq'::regclass)
 blog_id | integer |           |          | 
 author  | text    |           |          | 
 comment | text    |           |          | 
Indexes:
    "blog_comments__pk" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "comments_ref_blogs" FOREIGN KEY (blog_id) REFERENCES blogs(id)

When we want that Franck is not anymore able to create blogs and to comment on blogs we could do something like this:

postgres=# alter table blogs add constraint no_franck_blogs check ( author ~ '!^Franck' );
ALTER TABLE
postgres=# alter table blog_comments add constraint no_franck_comments check ( author ~ '!^Franck' );
ALTER TABLE

This will prevent Franck (actually it will prevent all people called Franck, but this is good in that case as we do not like people called Franck anymore) from inserting anything into these two tables:

postgres=# insert into blogs (author,blog) values ('Franck Pachot','another great blog');
ERROR:  new row for relation "blogs" violates check constraint "no_franck_blogs"
DETAIL:  Failing row contains (1, Franck Pachot, another great blog).

(Btw: Did you notice that you can use regular expressions in check constraints?)

This works and does what we want it to do. But there is an easier way of doing it. Currently we need to maintain two check constraints which are doing the same thing. By creating a domain we can centralize that:

postgres=# create domain no_franck_anymore as text check (value ~ '!^Franck' );
CREATE DOMAIN

Once we have that we can use the domain in our tables:

postgres=# alter table blogs drop constraint no_franck_blogs;
ALTER TABLE
postgres=# alter table blog_comments drop constraint no_franck_comments;
ALTER TABLE
postgres=# alter table blogs alter column author type no_franck_anymore;
ALTER TABLE
postgres=# alter table blog_comments alter column author type no_franck_anymore;
ALTER TABLE
postgres=# d blogs
                                 Table "public.blogs"
 Column |       Type        | Collation | Nullable |              Default              
--------+-------------------+-----------+----------+-----------------------------------
 id     | integer           |           | not null | nextval('blogs_id_seq'::regclass)
 author | no_franck_anymore |           |          | 
 blog   | text              |           |          | 
Indexes:
    "blogs_pk" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "blog_comments" CONSTRAINT "comments_ref_blogs" FOREIGN KEY (blog_id) REFERENCES blogs(id)

postgres=# d blog_comments
                                  Table "public.blog_comments"
 Column  |       Type        | Collation | Nullable |                  Default                  
---------+-------------------+-----------+----------+-------------------------------------------
 id      | integer           |           | not null | nextval('blog_comments_id_seq'::regclass)
 blog_id | integer           |           |          | 
 author  | no_franck_anymore |           |          | 
 comment | text              |           |          | 
Indexes:
    "blog_comments__pk" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "comments_ref_blogs" FOREIGN KEY (blog_id) REFERENCES blogs(id)

This still prevents Franck from blogging:

postgres=# insert into blogs (author,blog) values ('Franck Pachot','another great blog');
ERROR:  value for domain no_franck_anymore violates check constraint "no_franck_anymore_check"

… but we only need to maintain one domain and not two or more check constraints.