Home
About Me

Domains in Postgres

Postgres gives a way to define our own types, with attached rules. They are called domains. Domains are reusable, encapsulate type information so your table definition isn’t cluttered, and have the same storage as their base type.


Using a domain

We can use a domain to type cast.

1
select 'john@standard-oil.com'::email; ✅
2


3
select 'johnstandard-oil.com'::email; ❌
4
-- ERROR:  value for domain email violates check constraint "is_valid_email_address"


We can use a domain on table definitions.

1
create extension if not exists "uuid-ossp";
2


3
create table "user" (
4
  id uuid not null default uuid_generate_v4(),
5
  email email not null
6
);
7


8
insert into "user" (email) values ('not-an-email'); ❌
9
-- ERROR:  value for domain email violates check constraint "is_valid_email_address"


We can pass it in to a function where the underlying type is expected.

1
select length('john@standard-oil.com'::email); -- ✅ 21


We can define functions that accept it as input.

1
create function get_email_username(e email)
2
returns text as $$
3
  select split_part(e::text, '@', 1);
4
$$ language sql;
5


6
select get_email_username('john@standard-oil.com'); ✅ john
7
select get_email_username('not-an-email'); ❌
8
-- ERROR:  value for domain email violates check constraint "is_valid_email_address"


We can use domains in arrays. The check is enforced per element.

1
create table newsletter (
2
  subscribers email[] not null
3
);
4


5
insert into newsletter (subscribers)
6
values (array['hello@gmail.com', 'not-an-email']); ❌
7
-- ERROR:  value for domain email violates check constraint "is_valid_email_address"
8
-- entire statement fails


We can use domains in composite types.

1
create type user_info as (
2
  id uuid,
3
  email email
4
);
5


6
select row(uuid_generate_v4(), 'john@standard-oil.com')::user_info;


We can view the domain in psql like this.

1
\dD email

Create a domain

Create a domain like this:

1
-- Regex taken from here: https://stackoverflow.com/questions/201323/how-can-i-validate-an-email-address-using-a-regular-expression
2
create domain email as text constraint is_valid_email_address check (
3
    value ~* '^(?:[a-z0-9!#$%&''*+\x2f=?^_`\x7b-\x7d~\x2d]+(?:\.[a-z0-9!#$%&''*+\x2f=?^_`\x7b-\x7d~\x2d]+)*|"(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*")@(?:(?:[a-z0-9](?:[a-z0-9\x2d]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9\x2d]*[a-z0-9])?|\[(?:(?:(2(5[0-5]|[0-4][0-9])|1[0-9][0-9]|[1-9]?[0-9]))\.){3}(?:(2(5[0-5]|[0-4][0-9])|1[0-9][0-9]|[1-9]?[0-9])|[a-z0-9\x2d]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])$'
4
);


We can use a more complicated check constraint too, with a function. The function just has to be immutable (Postgres-speak for “deterministic”).

1
-- is_valid_pathname function definition omitted
2
create domain pathname as text constraint is_valid_pathname check (is_valid_pathname (value));
3


4
select '/são paulo'::pathname; ❌
5
-- ERROR:  value for domain pathname violates check constraint "is_valid_pathname"
6


7
select '/s%C3%A3o%20paulo'::pathname; ✅


We can also define multiple, separate constraints.

1
create domain ssn as text
2
  constraint correct_length check (length(value) = 11)
3
  constraint valid_pattern check (value ~ '^[0-9]{3}-[0-9]{2}-[0-9]{4}$')
4
  constraint no_all_zero_blocks check (value !~ '000|00-|0000$');


You can define not null constraints as well as defaults on a domain, but this information really belongs on the table. The Postgres docs even mention this being a bad practice.

1
-- you probaby shouldn't do this
2
create domain even_number as integer
3
  not null
4
  default 2
5
  check (mod(value, 2) = 0);


You can define a default collation for a domain.

1
create collation human_readable (
2
  provider = icu,
3
  locale = 'und-u-kn-true'
4
);
5


6
create domain sku as text
7
  collate "human_readable"
8
  check (value ~ '^SKU-[0-9]+$');
9


10
create table products (
11
  code sku primary key
12
);
13


14
insert into products (code) values
15
  ('SKU-2'), ('SKU-10'), ('SKU-100'), ('SKU-002');
16


17
select code from products order by code;
18
-- SKU-2
19
-- SKU-002
20
-- SKU-10
21
-- SKU-100

Wow! You read the whole thing. People who make it this far sometimes want to receive emails when I post something new.

I also have an RSS feed.