diff options
Diffstat (limited to 'doc/src/sgml/man7/CREATE_DOMAIN.7')
| -rw-r--r-- | doc/src/sgml/man7/CREATE_DOMAIN.7 | 195 |
1 files changed, 195 insertions, 0 deletions
diff --git a/doc/src/sgml/man7/CREATE_DOMAIN.7 b/doc/src/sgml/man7/CREATE_DOMAIN.7 new file mode 100644 index 0000000..796d545 --- /dev/null +++ b/doc/src/sgml/man7/CREATE_DOMAIN.7 @@ -0,0 +1,195 @@ +'\" t +.\" Title: CREATE DOMAIN +.\" Author: The PostgreSQL Global Development Group +.\" Generator: DocBook XSL Stylesheets v1.79.1 <http://docbook.sf.net/> +.\" Date: 2021 +.\" Manual: PostgreSQL 13.4 Documentation +.\" Source: PostgreSQL 13.4 +.\" Language: English +.\" +.TH "CREATE DOMAIN" "7" "2021" "PostgreSQL 13.4" "PostgreSQL 13.4 Documentation" +.\" ----------------------------------------------------------------- +.\" * Define some portability stuff +.\" ----------------------------------------------------------------- +.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +.\" http://bugs.debian.org/507673 +.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html +.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +.ie \n(.g .ds Aq \(aq +.el .ds Aq ' +.\" ----------------------------------------------------------------- +.\" * set default formatting +.\" ----------------------------------------------------------------- +.\" disable hyphenation +.nh +.\" disable justification (adjust text to left margin only) +.ad l +.\" ----------------------------------------------------------------- +.\" * MAIN CONTENT STARTS HERE * +.\" ----------------------------------------------------------------- +.SH "NAME" +CREATE_DOMAIN \- define a new domain +.SH "SYNOPSIS" +.sp +.nf +CREATE DOMAIN \fIname\fR [ AS ] \fIdata_type\fR + [ COLLATE \fIcollation\fR ] + [ DEFAULT \fIexpression\fR ] + [ \fIconstraint\fR [ \&.\&.\&. ] ] + +where \fIconstraint\fR is: + +[ CONSTRAINT \fIconstraint_name\fR ] +{ NOT NULL | NULL | CHECK (\fIexpression\fR) } +.fi +.SH "DESCRIPTION" +.PP +\fBCREATE DOMAIN\fR +creates a new domain\&. A domain is essentially a data type with optional constraints (restrictions on the allowed set of values)\&. The user who defines a domain becomes its owner\&. +.PP +If a schema name is given (for example, +CREATE DOMAIN myschema\&.mydomain \&.\&.\&.) then the domain is created in the specified schema\&. Otherwise it is created in the current schema\&. The domain name must be unique among the types and domains existing in its schema\&. +.PP +Domains are useful for abstracting common constraints on fields into a single location for maintenance\&. For example, several tables might contain email address columns, all requiring the same CHECK constraint to verify the address syntax\&. Define a domain rather than setting up each table\*(Aqs constraint individually\&. +.PP +To be able to create a domain, you must have +USAGE +privilege on the underlying type\&. +.SH "PARAMETERS" +.PP +\fIname\fR +.RS 4 +The name (optionally schema\-qualified) of a domain to be created\&. +.RE +.PP +\fIdata_type\fR +.RS 4 +The underlying data type of the domain\&. This can include array specifiers\&. +.RE +.PP +\fIcollation\fR +.RS 4 +An optional collation for the domain\&. If no collation is specified, the underlying data type\*(Aqs default collation is used\&. The underlying type must be collatable if +COLLATE +is specified\&. +.RE +.PP +DEFAULT \fIexpression\fR +.RS 4 +The +DEFAULT +clause specifies a default value for columns of the domain data type\&. The value is any variable\-free expression (but subqueries are not allowed)\&. The data type of the default expression must match the data type of the domain\&. If no default value is specified, then the default value is the null value\&. +.sp +The default expression will be used in any insert operation that does not specify a value for the column\&. If a default value is defined for a particular column, it overrides any default associated with the domain\&. In turn, the domain default overrides any default value associated with the underlying data type\&. +.RE +.PP +CONSTRAINT \fIconstraint_name\fR +.RS 4 +An optional name for a constraint\&. If not specified, the system generates a name\&. +.RE +.PP +NOT NULL +.RS 4 +Values of this domain are prevented from being null (but see notes below)\&. +.RE +.PP +NULL +.RS 4 +Values of this domain are allowed to be null\&. This is the default\&. +.sp +This clause is only intended for compatibility with nonstandard SQL databases\&. Its use is discouraged in new applications\&. +.RE +.PP +CHECK (\fIexpression\fR) +.RS 4 +CHECK +clauses specify integrity constraints or tests which values of the domain must satisfy\&. Each constraint must be an expression producing a Boolean result\&. It should use the key word +VALUE +to refer to the value being tested\&. Expressions evaluating to TRUE or UNKNOWN succeed\&. If the expression produces a FALSE result, an error is reported and the value is not allowed to be converted to the domain type\&. +.sp +Currently, +CHECK +expressions cannot contain subqueries nor refer to variables other than +VALUE\&. +.sp +When a domain has multiple +CHECK +constraints, they will be tested in alphabetical order by name\&. (PostgreSQL +versions before 9\&.5 did not honor any particular firing order for +CHECK +constraints\&.) +.RE +.SH "NOTES" +.PP +Domain constraints, particularly +NOT NULL, are checked when converting a value to the domain type\&. It is possible for a column that is nominally of the domain type to read as null despite there being such a constraint\&. For example, this can happen in an outer\-join query, if the domain column is on the nullable side of the outer join\&. A more subtle example is +.sp +.if n \{\ +.RS 4 +.\} +.nf +INSERT INTO tab (domcol) VALUES ((SELECT domcol FROM tab WHERE false)); +.fi +.if n \{\ +.RE +.\} +.sp +The empty scalar sub\-SELECT will produce a null value that is considered to be of the domain type, so no further constraint checking is applied to it, and the insertion will succeed\&. +.PP +It is very difficult to avoid such problems, because of SQL\*(Aqs general assumption that a null value is a valid value of every data type\&. Best practice therefore is to design a domain\*(Aqs constraints so that a null value is allowed, and then to apply column +NOT NULL +constraints to columns of the domain type as needed, rather than directly to the domain type\&. +.PP +PostgreSQL +assumes that +CHECK +constraints\*(Aq conditions are immutable, that is, they will always give the same result for the same input value\&. This assumption is what justifies examining +CHECK +constraints only when a value is first converted to be of a domain type, and not at other times\&. (This is essentially the same as the treatment of table +CHECK +constraints, as described in +Section\ \&5.4.1\&.) +.PP +An example of a common way to break this assumption is to reference a user\-defined function in a +CHECK +expression, and then change the behavior of that function\&. +PostgreSQL +does not disallow that, but it will not notice if there are stored values of the domain type that now violate the +CHECK +constraint\&. That would cause a subsequent database dump and reload to fail\&. The recommended way to handle such a change is to drop the constraint (using +\fBALTER DOMAIN\fR), adjust the function definition, and re\-add the constraint, thereby rechecking it against stored data\&. +.SH "EXAMPLES" +.PP +This example creates the +us_postal_code +data type and then uses the type in a table definition\&. A regular expression test is used to verify that the value looks like a valid US postal code: +.sp +.if n \{\ +.RS 4 +.\} +.nf +CREATE DOMAIN us_postal_code AS TEXT +CHECK( + VALUE ~ \*(Aq^\ed{5}$\*(Aq +OR VALUE ~ \*(Aq^\ed{5}\-\ed{4}$\*(Aq +); + +CREATE TABLE us_snail_addy ( + address_id SERIAL PRIMARY KEY, + street1 TEXT NOT NULL, + street2 TEXT, + street3 TEXT, + city TEXT NOT NULL, + postal us_postal_code NOT NULL +); +.fi +.if n \{\ +.RE +.\} +.SH "COMPATIBILITY" +.PP +The command +\fBCREATE DOMAIN\fR +conforms to the SQL standard\&. +.SH "SEE ALSO" +ALTER DOMAIN (\fBALTER_DOMAIN\fR(7)), DROP DOMAIN (\fBDROP_DOMAIN\fR(7)) |