diff options
Diffstat (limited to 'doc/src/sgml/html/sql-createdomain.html')
| -rw-r--r-- | doc/src/sgml/html/sql-createdomain.html | 151 |
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 |