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
- PostgreSQL 9.1
- collation support added (commit 414c5a2e)
- PostgreSQL 7.4
ALTER DOMAINcommand added (commit 17194f41)- support for
CHECKexpressions added (commit 6b603e67)
- PostgreSQL 7.3
- added (commit 01c76f74)
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"
References
- PostgreSQL documentation: Domain types
- PostgreSQL documentation: CREATE DOMAIN (see section "Notes")