On 2019-10-31 15:15, Daniel Miller dmiller@... [firebird-support]
wrote:

> The fault or misunderstanding is probably mine, but:
>
> I thought the "identity" definition was functionally the same as the
> before-insert with generator. Particularly with regards to handling
> null
> values during inserts. However, given:
>
> create table TEST1 (
> TEST_ID integer generated by default as identity primary key
> TEST_NAME varchar(10)
> );
>
> Attempting to execute:
>
> update or insert into TEST1 (TEST_ID, TEST_NAME)
> values (null, 'abc') matching (TEST_ID)
>
> results in:
> validation error for column "TEST1"."TEST_ID", value "*** null ***".
>
>
> However, the same query works without issue with a before-insert
> trigger
> using a generator. My question - is this intended behavior? Or a bug in
> the identity implementation?

Your expectation is wrong. The identity is working as intended. The
"generated by default as identity" means that the identity is only used
when you don't explicitly populate the TEST_ID column. Your code,
however, is explicitly populating the TEST_ID column with null, so the
identity is not applied.

This is the major difference with using a sequence + trigger: in the
trigger you have more flexibility when to generate or not.

Also usage of UPDATE OR INSERT doesn't make sense here: MATCHING
(TEST_ID) will never match, so you might just as well use INSERT INTO
TEST1 (TESTNAME) VALUES ('abc') directly.

Mark