Home Explore Blog CI



postgresql

16th chunk of `doc/src/sgml/ref/create_table.sgml`
e2a5ae96286edb7d9dd7ff7b37851a16e3482479da1d5dac0000000100000fb0
 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 other generated columns.  Any functions and operators used must be
      immutable.  References to other tables are not allowed.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="sql-createtable-parms-generated-identity">
    <term><literal>GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ]</literal></term>
    <listitem>
     <para>
      This clause creates the column as an <firstterm>identity
      column</firstterm>.  It will have an implicit sequence attached to it
      and in newly-inserted rows the column will automatically have values
      from the sequence assigned to it.
      Such a column is implicitly <literal>NOT NULL</literal>.
     </para>

     <para>
      The clauses <literal>ALWAYS</literal> and <literal>BY DEFAULT</literal>
      determine how explicitly user-specified values are handled in
      <command>INSERT</command> and <command>UPDATE</command> commands.
     </para>

     <para>
      In an <command>INSERT</command> command, if <literal>ALWAYS</literal> is
      selected, a user-specified value is only accepted if the
      <command>INSERT</command> statement specifies <literal>OVERRIDING SYSTEM
      VALUE</literal>.  If <literal>BY DEFAULT</literal> is selected, then the
      user-specified value takes precedence.  See <xref linkend="sql-insert"/>
      for details.  (In the <command>COPY</command> command, user-specified
      values are always used regardless of this setting.)
     </para>

     <para>
      In an <command>UPDATE</command> command, if <literal>ALWAYS</literal> is
      selected, any update of the column to any value other than
      <literal>DEFAULT</literal> will be rejected.  If <literal>BY
      DEFAULT</literal> is selected, the column can be updated normally.
      (There is no <literal>OVERRIDING</literal> clause for the
      <command>UPDATE</command> command.)
     </para>

     <para>
      The optional <replaceable>sequence_options</replaceable> clause can
      be used to override the parameters of the sequence.  The available
      options include those shown for <xref linkend="sql-createsequence"/>,
      plus <literal>SEQUENCE NAME <replaceable>name</replaceable></literal>,
      <literal>LOGGED</literal>, and <literal>UNLOGGED</literal>, which
      allow selection of the name and persistence level of the
      sequence.  Without <literal>SEQUENCE NAME</literal>, the system
      chooses an unused name for the sequence.
      Without <literal>LOGGED</literal> or <literal>UNLOGGED</literal>,
      the sequence will have the same persistence level as the table.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="sql-createtable-parms-unique">
    <term><literal>UNIQUE [ NULLS [ NOT ] DISTINCT ]</literal> (column constraint)</term>
    <term><literal>UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, <replaceable class="parameter">column_name</replaceable>

Title: CREATE TABLE Parameters: GENERATED ALWAYS/BY DEFAULT AS IDENTITY and UNIQUE
Summary
The documentation details the `GENERATED ALWAYS AS` clause for creating generated columns. These columns can be `VIRTUAL` (computed on read, no storage) or `STORED` (computed on write, stored on disk). The expression for a generated column can reference other columns, but not other generated columns. It also covers the `GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY` clause for creating identity columns with automatic sequence-assigned values. `ALWAYS` rejects user-specified values during `INSERT` unless `OVERRIDING SYSTEM VALUE` is specified. It rejects updates. `BY DEFAULT` allows user-specified values during `INSERT` and allows updates. Sequence options can be specified. Finally, the documentation briefly introduces the `UNIQUE` constraint.