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

On 2019-02-22 15:12, Dean Rasheed wrote:
> In particular, Syntax Rule 11b of section 14.11 says that an INSERT
> statement on a GENERATED ALWAYS identity column must specify an
> overriding clause, but it doesn't place any restriction on the type of
> overriding clause allowed. In other words it should be possible to use
> either OVERRIDING SYSTEM VALUE or OVERRIDING USER VALUE, but we
> currently throw an error unless it's the former.

It appears you are right.

> - 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.

> 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?

> <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.

> - 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?

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services