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

Peter Eisentraut <peter.eisentraut@xxxxxxxxxxxxxxx> · Wed, 11 Dec 2019 16:24:37 +0100

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