summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/sql-createdomain.html
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-13 13:44:03 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-13 13:44:03 +0000
commit293913568e6a7a86fd1479e1cff8e2ecb58d6568 (patch)
treefc3b469a3ec5ab71b36ea97cc7aaddb838423a0c /doc/src/sgml/html/sql-createdomain.html
parentInitial commit. (diff)
downloadpostgresql-16-293913568e6a7a86fd1479e1cff8e2ecb58d6568.tar.xz
postgresql-16-293913568e6a7a86fd1479e1cff8e2ecb58d6568.zip
Adding upstream version 16.2.upstream/16.2
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'doc/src/sgml/html/sql-createdomain.html')
-rw-r--r--doc/src/sgml/html/sql-createdomain.html151
1 files changed, 151 insertions, 0 deletions
diff --git a/doc/src/sgml/html/sql-createdomain.html b/doc/src/sgml/html/sql-createdomain.html
new file mode 100644
index 0000000..8d7a06b
--- /dev/null
+++ b/doc/src/sgml/html/sql-createdomain.html
@@ -0,0 +1,151 @@
+<?xml version="1.0" encoding="UTF-8" standalone="no"?>
+<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>CREATE DOMAIN</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-createdatabase.html" title="CREATE DATABASE" /><link rel="next" href="sql-createeventtrigger.html" title="CREATE EVENT TRIGGER" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">CREATE DOMAIN</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-createdatabase.html" title="CREATE DATABASE">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-createeventtrigger.html" title="CREATE EVENT TRIGGER">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-CREATEDOMAIN"><div class="titlepage"></div><a id="id-1.9.3.62.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">CREATE DOMAIN</span></h2><p>CREATE DOMAIN — define a new domain</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">
+CREATE DOMAIN <em class="replaceable"><code>name</code></em> [ AS ] <em class="replaceable"><code>data_type</code></em>
+ [ COLLATE <em class="replaceable"><code>collation</code></em> ]
+ [ DEFAULT <em class="replaceable"><code>expression</code></em> ]
+ [ <em class="replaceable"><code>constraint</code></em> [ ... ] ]
+
+<span class="phrase">where <em class="replaceable"><code>constraint</code></em> is:</span>
+
+[ CONSTRAINT <em class="replaceable"><code>constraint_name</code></em> ]
+{ NOT NULL | NULL | CHECK (<em class="replaceable"><code>expression</code></em>) }
+</pre></div><div class="refsect1" id="id-1.9.3.62.5"><h2>Description</h2><p>
+ <code class="command">CREATE DOMAIN</code> 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.
+ </p><p>
+ If a schema name is given (for example, <code class="literal">CREATE DOMAIN
+ myschema.mydomain ...</code>) 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.
+ </p><p>
+ 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's constraint
+ individually.
+ </p><p>
+ To be able to create a domain, you must have <code class="literal">USAGE</code>
+ privilege on the underlying type.
+ </p></div><div class="refsect1" id="id-1.9.3.62.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>name</code></em></span></dt><dd><p>
+ The name (optionally schema-qualified) of a domain to be created.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>data_type</code></em></span></dt><dd><p>
+ The underlying data type of the domain. This can include array
+ specifiers.
+ </p></dd><dt><span class="term"><em class="replaceable"><code>collation</code></em></span></dt><dd><p>
+ An optional collation for the domain. If no collation is
+ specified, the domain has the same collation behavior as its
+ underlying data type.
+ The underlying type must be collatable if <code class="literal">COLLATE</code>
+ is specified.
+ </p></dd><dt><span class="term"><code class="literal">DEFAULT <em class="replaceable"><code>expression</code></em></code></span></dt><dd><p>
+ The <code class="literal">DEFAULT</code> 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.
+ </p><p>
+ 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.
+ </p></dd><dt><span class="term"><code class="literal">CONSTRAINT <em class="replaceable"><code>constraint_name</code></em></code></span></dt><dd><p>
+ An optional name for a constraint. If not specified,
+ the system generates a name.
+ </p></dd><dt><span class="term"><code class="literal">NOT NULL</code></span></dt><dd><p>
+ Values of this domain are prevented from being null
+ (but see notes below).
+ </p></dd><dt><span class="term"><code class="literal">NULL</code></span></dt><dd><p>
+ Values of this domain are allowed to be null. This is the default.
+ </p><p>
+ This clause is only intended for compatibility with
+ nonstandard SQL databases. Its use is discouraged in new
+ applications.
+ </p></dd><dt><span class="term"><code class="literal">CHECK (<em class="replaceable"><code>expression</code></em>)</code></span></dt><dd><p><code class="literal">CHECK</code> 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 <code class="literal">VALUE</code>
+ 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.
+ </p><p>
+ Currently, <code class="literal">CHECK</code> expressions cannot contain
+ subqueries nor refer to variables other than <code class="literal">VALUE</code>.
+ </p><p>
+ When a domain has multiple <code class="literal">CHECK</code> constraints,
+ they will be tested in alphabetical order by name.
+ (<span class="productname">PostgreSQL</span> versions before 9.5 did not honor any
+ particular firing order for <code class="literal">CHECK</code> constraints.)
+ </p></dd></dl></div></div><div class="refsect1" id="id-1.9.3.62.7"><h2>Notes</h2><p>
+ Domain constraints, particularly <code class="literal">NOT NULL</code>, 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
+</p><pre class="programlisting">
+INSERT INTO tab (domcol) VALUES ((SELECT domcol FROM tab WHERE false));
+</pre><p>
+ 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.
+ </p><p>
+ It is very difficult to avoid such problems, because of SQL's general
+ assumption that a null value is a valid value of every data type. Best practice
+ therefore is to design a domain's constraints so that a null value is allowed,
+ and then to apply column <code class="literal">NOT NULL</code> constraints to columns of
+ the domain type as needed, rather than directly to the domain type.
+ </p><p>
+ <span class="productname">PostgreSQL</span> assumes that
+ <code class="literal">CHECK</code> constraints' conditions are immutable, that is,
+ they will always give the same result for the same input value. This
+ assumption is what justifies examining <code class="literal">CHECK</code>
+ 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 <code class="literal">CHECK</code> constraints, as described in
+ <a class="xref" href="ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTS" title="5.4.1. Check Constraints">Section 5.4.1</a>.)
+ </p><p>
+ An example of a common way to break this assumption is to reference a
+ user-defined function in a <code class="literal">CHECK</code> expression, and then
+ change the behavior of that
+ function. <span class="productname">PostgreSQL</span> does not disallow that,
+ but it will not notice if there are stored values of the domain type that
+ now violate the <code class="literal">CHECK</code> constraint. That would cause a
+ subsequent database dump and restore to fail. The recommended way to
+ handle such a change is to drop the constraint (using <code class="command">ALTER
+ DOMAIN</code>), adjust the function definition, and re-add the
+ constraint, thereby rechecking it against stored data.
+ </p><p>
+ It's also good practice to ensure that domain <code class="literal">CHECK</code>
+ expressions will not throw errors.
+ </p></div><div class="refsect1" id="id-1.9.3.62.8"><h2>Examples</h2><p>
+ This example creates the <code class="type">us_postal_code</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:
+
+</p><pre class="programlisting">
+CREATE DOMAIN us_postal_code AS TEXT
+CHECK(
+ VALUE ~ '^\d{5}$'
+OR VALUE ~ '^\d{5}-\d{4}$'
+);
+
+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
+);
+</pre></div><div class="refsect1" id="SQL-CREATEDOMAIN-COMPATIBILITY"><h2>Compatibility</h2><p>
+ The command <code class="command">CREATE DOMAIN</code> conforms to the SQL
+ standard.
+ </p></div><div class="refsect1" id="SQL-CREATEDOMAIN-SEE-ALSO"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-alterdomain.html" title="ALTER DOMAIN"><span class="refentrytitle">ALTER DOMAIN</span></a>, <a class="xref" href="sql-dropdomain.html" title="DROP DOMAIN"><span class="refentrytitle">DROP DOMAIN</span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-createdatabase.html" title="CREATE DATABASE">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-createeventtrigger.html" title="CREATE EVENT TRIGGER">Next</a></td></tr><tr><td width="40%" align="left" valign="top">CREATE DATABASE </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="40%" align="right" valign="top"> CREATE EVENT TRIGGER</td></tr></table></div></body></html> \ No newline at end of file
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-21 19:15:28 +0000