Home • Explore • Blog • CI
☰



postgresql
3rd chunk of `doc/src/sgml/ref/create_domain.sgml`
6c01594e03a1e7ec94634d8ad2f5e330d2d529239191fe91000000010000094d
 </para>

     <para>
      Currently, <literal>CHECK</literal> expressions cannot contain
      subqueries nor refer to variables other than <literal>VALUE</literal>.
     </para>

     <para>
      When a domain has multiple <literal>CHECK</literal> constraints,
      they will be tested in alphabetical order by name.
      (<productname>PostgreSQL</productname> versions before 9.5 did not honor any
      particular firing order for <literal>CHECK</literal> constraints.)
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </refsect1>

 <refsect1 id="sql-createdomain-notes">
  <title>Notes</title>

  <para>
   Domain constraints, particularly <literal>NOT NULL</literal>, 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
<programlisting>
INSERT INTO tab (domcol) VALUES ((SELECT domcol FROM tab WHERE false));
</programlisting>
   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.
  </para>

  <para>
   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 <literal>NOT NULL</literal> constraints to columns of
   the domain type as needed, rather than directly to the domain type.
  </para>

  <para>
   <productname>PostgreSQL</productname> assumes that
   <literal>CHECK</literal> constraints' conditions are immutable, that is,
   they will always give the same result for the same input value.  This
   assumption is what justifies examining <literal>CHECK</literal>
   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 <literal>CHECK</literal> constraints, as described in
   <xref linkend="ddl-constraints-check-constraints"/>.)
  </para>

  <para>
   An example of a common way to break this assumption
Title: Notes on Domain Constraints and Null Values in PostgreSQL
Summary
This section discusses important notes regarding domain constraints, particularly NOT NULL, and how they interact with null values in PostgreSQL. It explains that a column of a domain type can read as null despite a NOT NULL constraint, especially in outer-join queries or when using empty scalar sub-SELECTs. The text emphasizes that best practice is to allow null values in domain constraints and apply NOT NULL constraints to columns of the domain type instead. It also clarifies that PostgreSQL assumes CHECK constraint conditions are immutable and examines them only when a value is first converted to the domain type, similar to table CHECK constraints.