Fix INSERT OVERRIDING USER VALUE behavior

The original implementation disallowed using OVERRIDING USER VALUE on
identity columns defined as GENERATED ALWAYS, which is not per
standard.  So allow that now.

Expand documentation and tests around this.

Author: Dean Rasheed <dean.a.rasheed@gmail.com>
Reviewed-by: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Reviewed-by: Vik Fearing <vik@postgresfriends.org>
Discussion: https://www.postgresql.org/message-id/flat/CAEZATCVrh2ufCwmzzM%3Dk_OfuLhTTPBJCdFkimst2kry4oHepuQ%40mail.gmail.com

3 files changed, 48 insertions, 20 deletions

diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 9f8b59de50a..155866c7c83 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -842,15 +842,27 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
 
      <para>
       The clauses <literal>ALWAYS</literal> and <literal>BY DEFAULT</literal>
-      determine how the sequence value is given precedence over a
-      user-specified value in an <command>INSERT</command> statement.
-      If <literal>ALWAYS</literal> is specified, 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 specified, 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.)
+      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>
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index e829c61642d..a6cec6b02ea 100644
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -206,10 +206,19 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
       <term><literal>OVERRIDING SYSTEM VALUE</literal></term>
       <listitem>
        <para>
-        Without this clause, it is an error to specify an explicit value
-        (other than <literal>DEFAULT</literal>) for an identity column defined
-        as <literal>GENERATED ALWAYS</literal>.  This clause overrides that
-        restriction.
+        If this clause is specified, then any values supplied for identity
+        columns will override the default sequence-generated values.
+       </para>
+
+       <para>
+        For an identity column defined as <literal>GENERATED ALWAYS</literal>,
+        it is an error to insert an explicit value (other than
+        <literal>DEFAULT</literal>) without specifying either
+        <literal>OVERRIDING SYSTEM VALUE</literal> or <literal>OVERRIDING USER
+        VALUE</literal>.  (For an identity column defined as
+        <literal>GENERATED BY DEFAULT</literal>, <literal>OVERRIDING SYSTEM
+        VALUE</literal> is the normal behavior and specifying it does nothing,
+        but <productname>PostgreSQL</productname> allows it as an extension.)
        </para>
       </listitem>
      </varlistentry>
@@ -219,8 +228,8 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
       <listitem>
        <para>
         If this clause is specified, then any values supplied for identity
-        columns defined as <literal>GENERATED BY DEFAULT</literal> are ignored
-        and the default sequence-generated values are applied.
+        columns are ignored and the default sequence-generated values are
+        applied.
        </para>
 
        <para>
@@ -238,7 +247,8 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
       <term><literal>DEFAULT VALUES</literal></term>
       <listitem>
        <para>
-        All columns will be filled with their default values.
+        All columns will be filled with their default values, as if
+        <literal>DEFAULT</literal> were explicitly specified for each column.
         (An <literal>OVERRIDING</literal> clause is not permitted in this
         form.)
        </para>
@@ -258,8 +268,11 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
       <term><literal>DEFAULT</literal></term>
       <listitem>
        <para>
-        The corresponding column will be filled with
-        its default value.
+        The corresponding column will be filled with its default value.  An
+        identity column will be filled with a new value generated by the
+        associated sequence.  For a generated column, specifying this is
+        permitted but merely specifies the normal behavior of computing the
+        column from its generation expression.
        </para>
       </listitem>
      </varlistentry>
diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml
index f58dcd8877b..4840bf560c1 100644
--- a/doc/src/sgml/ref/update.sgml
+++ b/doc/src/sgml/ref/update.sgml
@@ -142,8 +142,11 @@ UPDATE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [
     <term><literal>DEFAULT</literal></term>
     <listitem>
      <para>
-      Set the column to its default value (which will be NULL if no
-      specific default expression has been assigned to it).
+      Set the column to its default value (which will be NULL if no specific
+      default expression has been assigned to it).  An identity column will be
+      set to a new value generated by the associated sequence.  For a
+      generated column, specifying this is permitted but merely specifies the
+      normal behavior of computing the column from its generation expression.
      </para>
     </listitem>
    </varlistentry>