Domain

A user-defined data type based on an existing data type

A domain (sometimes referred to as a "derived type") is a user-defined data type based on an existing data type, typically to provide what is effectively a common definition of a check constraint, which can be used in multiple table definitions.

Caveats

Unlike a regular constraint, while any constraints applied to the domain will be checked when converting a column to the domain type, or inserting the column, they will not be checked retroactively if the domain definition is modified.

psql command(s)

  • \dD[S+] lists domains

Change history

Examples

Create a domain:

postgres=# CREATE DOMAIN git_sha1 AS
  CHAR(40) NOT NULL
  CHECK (VALUE ~ '^[0-9a-fA-F]+$');
CREATE DOMAIN

This, as the name implies, defines a domain suitable for storing a GIT sha1 tag.

List available domains:

postgres=# \dD
                                               List of domains
 Schema |   Name   |     Type      | Collation | Nullable | Default |                 Check                  
--------+----------+---------------+-----------+----------+---------+----------------------------------------
 public | git_sha1 | character(40) |           | not null |         | CHECK (VALUE ~ '^[0-9a-fA-F]+$'::text)
(1 row)

Create a table with a domain:

postgres=# CREATE TABLE object_commit_ref (
             repo TEXT NOT NULL,
             commit_ref GIT_SHA1,
             PRIMARY KEY(repo, commit_ref)
           );
CREATE TABLE

Insert valid data:

postgres=# INSERT INTO repo_commit_ref 
                VALUES('postgresql', 'd31084e9d1118b25fd16580d9d8c2924b5740dff');
INSERT 0 1

Attempt to insert invalid data:

postgres=# INSERT INTO object_commit_ref VALUES('foo', NULL);
ERROR:  domain git_sha1 does not allow null values

postgres=# INSERT INTO object_commit_ref VALUES('bar', 'baz');
ERROR:  value for domain git_sha1 violates check constraint "git_sha1_check"

Categories

Data type

See also

CREATE DOMAIN, ALTER DOMAIN, DROP DOMAIN