Home • Explore • Blog • CI
☰



postgresql
15th chunk of `doc/src/sgml/ref/create_table.sgml`
f21395fb95fe0bfeb8808640e7499caebdba522d1cd0bd3b0000000100000fa1
 INHERIT</literal> will not propagate to
      child tables.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="sql-createtable-parms-null">
    <term><literal>NULL</literal></term>
    <listitem>
     <para>
      The column is allowed to contain null values. This is the default.
     </para>

     <para>
      This clause is only provided for compatibility with
      non-standard SQL databases.  Its use is discouraged in new
      applications.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="sql-createtable-parms-check">
    <term><literal>CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] </literal></term>
    <listitem>
     <para>
      The <literal>CHECK</literal> clause specifies an expression producing a
      Boolean result which new or updated rows must satisfy for an
      insert or update operation to succeed.  Expressions evaluating
      to TRUE or UNKNOWN succeed.  Should any row of an insert or
      update operation produce a FALSE result, an error exception is
      raised and the insert or update does not alter the database.  A
      check constraint specified as a column constraint should
      reference that column's value only, while an expression
      appearing in a table constraint can reference multiple columns.
     </para>

     <para>
      Currently, <literal>CHECK</literal> expressions cannot contain
      subqueries nor refer to variables other than columns of the
      current row (see <xref linkend="ddl-constraints-check-constraints"/>).
      The system column <literal>tableoid</literal>
      may be referenced, but not any other system column.
     </para>

     <para>
      A constraint marked with <literal>NO INHERIT</literal> will not propagate to
      child tables.
     </para>

     <para>
      When a table has multiple <literal>CHECK</literal> constraints,
      they will be tested for each row in alphabetical order by name,
      after checking <literal>NOT NULL</literal> constraints.
      (<productname>PostgreSQL</productname> versions before 9.5 did not honor any
      particular firing order for <literal>CHECK</literal> constraints.)
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="sql-createtable-parms-default">
    <term><literal>DEFAULT
    <replaceable>default_expr</replaceable></literal></term>
    <listitem>
     <para>
      The <literal>DEFAULT</literal> clause assigns a default data value for
      the column whose column definition it appears within.  The value
      is any variable-free expression (in particular, cross-references
      to other columns in the current table are not allowed).  Subqueries
      are not allowed either.  The data type of the default expression must
      match the data type of the column.
     </para>

     <para>
      The default expression will be used in any insert operation that
      does not specify a value for the column.  If there is no default
      for a column, then the default is null.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="sql-createtable-parms-generated-stored">
    <term><literal>GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) [ STORED | VIRTUAL ]</literal><indexterm><primary>generated column</primary></indexterm></term>
    <listitem>
     <para>
      This clause creates the column as a <firstterm>generated
      column</firstterm>.  The column cannot be written to, and when read the
      result of the specified expression will be returned.
     </para>

     <para>
      When <literal>VIRTUAL</literal> is specified, the column will be
      computed when it is read, and it will not occupy any storage.  When
      <literal>STORED</literal> is specified, the column will be computed on
      write and will be stored on disk.  <literal>VIRTUAL</literal> is the
      default.
     </para>

     <para>
      The generation expression can refer to other columns in the table, but
      not
Title: CREATE TABLE Parameters: NULL, CHECK, DEFAULT, and GENERATED
Summary
This section describes more `CREATE TABLE` parameters, starting with `NULL`, used to allow null values in a column (though discouraged in new applications). It then details the `CHECK` constraint, ensuring new or updated rows satisfy a Boolean expression. `CHECK` constraints cannot contain subqueries or reference variables other than columns of the current row. They are tested alphabetically by name after `NOT NULL` constraints. The `DEFAULT` clause assigns a default value to a column if none is specified during insertion. Finally, it explains the `GENERATED ALWAYS AS` clause, creating a generated column whose value is derived from an expression. These can be `VIRTUAL` (computed on read, no storage) or `STORED` (computed on write, stored on disk).