So I started looking into the bug noted in [1], but before getting to
multi-row inserts, I concluded that the current single-row behaviour
isn't spec-compliant.
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's useful to allow OVERRIDING USER VALUE for precisely the example
use-case given in the INSERT docs:
This clause is useful for example when copying values between tables.
Writing <literal>INSERT INTO tbl2 OVERRIDING USER VALUE SELECT * FROM
tbl1</literal> will copy from <literal>tbl1</literal> all columns that
are not identity columns in <literal>tbl2</literal> while values for
the identity columns in <literal>tbl2</literal> will be generated by
the sequences associated with <literal>tbl2</literal>.
which currently only works for a GENERATED BY DEFAULT identity column,
but should work equally well for a GENERATED ALWAYS identity column.
So I propose the attached patch.
Regards,
Dean
[1] https://postgr.es/m/CAEZATCUmSp3-8nLOpgGcPkpUEXK9TJGM%3DiA6q4E2Sn%3D%2BbwkKNA%40mail.gmail.com
