Never mind, I withdraw my question, I had overlooked the fact that this
is a direct equivalent of INSERT INTO (id, colval) values (null, 'abc'),
which doesn't make much sense either.
Mark
On 20-11-2020 10:01, Mark Rotteveel wrote:
Triggered by the question by Walter R. Ojeda Valient on
firebird-support, I'm wondering if the null validation error that occurs
in Firebird 3 with UPDATE OR INSERT is intentional.
Specifically, with table:
```
create table uoi_example2 (
id integer generated by default as identity constraint
pk_uoi_example2 primary key,
colval varchar(50)
);
```
The following UPDATE OR INSERT produces an error:
```
update or insert into uoi_example2(id, colval) values (null, 'abc');
```
Error:
Statement failed, SQLSTATE = 23000
validation error for column "UOI_EXAMPLE2"."ID", value "*** null ***"
I know that using this statement doesn't make much sense (an INSERT
would be much simpler), but I can guess that there are cases where the
parameterized equivalent could make sense to do.
In Firebird 2.5, the equivalent using a trigger does work:
```
create table uoi_example2 (
id integer constraint pk_uoi_example2 primary key,
colval varchar(50)
);
create sequence sq_uoi_example2_id;
set term #;
create trigger bi_uoi_example before insert on uoi_example2
as
begin
if (new.id is null) then
begin
new.id = next value for sq_uoi_example2_id;
end
end#
set term ;#
commit;
update or insert into uoi_example2(id, colval) values (null, 'abc');
```
In short, was this change intentional, or should this be considered a bug?
Mark
--
Mark Rotteveel
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
