Re: INSERT ... OVERRIDING USER VALUE vs GENERATED ALWAYS identity columns

On Mon, 25 Feb 2019 at 12:47, Peter Eisentraut
<peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
> > - and the column in new rows will automatically have values from the
> > - sequence assigned to it.
> > + and new rows in the column will automatically have values from the
> > + sequence assigned to them.
>
> The "it" refers to "the column", so I think it's correct.
>

Ah, OK. I failed to parse the original wording.

> > 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
> > + precedence, unless the <command>INSERT</command> statement
> specifies
> > + <literal>OVERRIDING USER VALUE</literal>.
> > + See <xref linkend="sql-insert"/> for details. (In
>
> Isn't your change that it now applies to both ALWAYS and BY DEFAULT? So
> why attach this phrase to the BY DEFAULT explanation?
>

The last couple of sentences of that paragraph are describing the
circumstances under which the user-specified value will be applied. So
for the ALWAYS case, it's only if OVERRIDING SYSTEM VALUE is
specified, and for the BY DEFAULT case, it's only if OVERRIDING USER
VALUE isn't specified. Without that additional text, the original
wording could be taken to mean that for a BY DEFAULT column, the
user-specified value always gets applied.

> > <para>
> > + Additionally, if <literal>ALWAYS</literal> is specified, any
> attempt to
> > + update the value of the column using an <command>UPDATE</command>
> > + statement specifying any value other than
> <literal>DEFAULT</literal>
> > + will be rejected. If <literal>BY DEFAULT</literal> is
> specified, the
> > + system will allow values in the column to be updated.
> > + </para>
>
> This is already documented on the INSERT reference page.
>

I can't see anywhere where we document how UPDATE behaves with identity columns.

> > - errhint("Use OVERRIDING SYSTEM VALUE to override.")));
> > + errhint("You must specify either OVERRIDING SYSTEM VALUE or
> OVERRIDING USER VALUE.")));
>
> Is this a good hint? If the user wanted to insert something, then
> specifying OVERRIDING USER VALUE won't really accomplish that.
> OVERRIDING USER VALUE is only useful in the specific situations that the
> documentation discussed. Can we detect those?
>

Hmm, I'm not sure that we reliably guess what the user intended. What
exactly did you have in mind?

Regards,
Dean