Re: Identity columns, DEFAULT keyword and multi-row inserts

On 2019-12-10 17:53, Tom Lane wrote:
>> However, a multi-row insert like the following:
>> insert into test (id, data)
>> values
>> (default,1),
>> (default,2);
>> fails with:
>> ERROR: cannot insert into column "id"
>> Detail: Column "id" is an identity column defined as GENERATED ALWAYS.
>> Hint: Use OVERRIDING SYSTEM VALUE to override.
> Yeah, in principle a multi-row INSERT could grovel through all the
> rows of the VALUES clause and confirm that every one of them has
> DEFAULT there. Not sure it's worth the trouble, but if it's not
> a lot of code then maybe. It certainly seems a bit inconsistent.

It looks like the multi-row case in transformInsertStmt() would have to
develop a bit more smarts to discover this case and then replace the RTE
reference in the target list with a single SetToDefault node?

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