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 DOMAIN
command added (commit 17194f41)- support for
CHECK
expressions 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")