Re: Identity column behavior discrepancies when inserting one or many rows

On Mon, Oct 12, 2020 at 4:04 AM Jean Prulière <jean(at)oclock(dot)io> wrote:

> Here is a very simple script to reproduce what we encountered :
>
> CREATE TABLE test (
>> id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
>> sometext text NOT NULL
>> );
>>
>> -- that one works : INSERT 0 1
>> -- I think it should not and throw a 428C9
>> INSERT INTO test (id, sometext)
>>
> VALUES (DEFAULT, 'test');
>>
>> -- this one does not : SQL state : 428C9
>> INSERT INTO test (id, sometext)
>>
> VALUES (DEFAULT, 'test2'),
>> (DEFAULT, 'test3');
>>
>
> Though GENERATED ALWAYS implies the absence of the identity column in the
> column names list of any INSERT statement, listing it there is OK, without
> the OVERRIDING SYSTEM VALUE flag, as long as only one row is inserted (and
> DEFAULT is used as value, of course). But starting at 2 rows (and I can
> only guess it never stops), the proper error is thrown, advising to use the
> aforementioned flag.
>
> Is there any reason I missed such behavior would be expected ?
>

I agree the inconsistency seems to be undocumented but I wonder why the
second case doesn't work, not why the first one does. INSERT says: "For a
generated column, specifying this is permitted but merely specifies the
normal behavior of computing the column from its generation expression."
That says the first case works, default means the same as omitting the
column altogether.

David J.