How to create an optional default value?

[MCMartin]

CREATE TABLE "DBA"."test2" (
"id" BIGINT NOT NULL DEFAULT AUTOINCREMENT,
"advancedid" VARCHAR(250) NOT NULL UNIQUE,
PRIMARY KEY ( "id" ASC ))

How to achieve, that if an insert is not containing a value for advancedid that the value of id for the inserted row is used and that if the value is provided the value from the insert is used? So no value => use id. If a value is provided use that value.

[VolkerBarth · Accepted solution]

I was about to suggest using a DEFAULT AUTOINCREMENT but that doesn't work as soon as you really insert a non-default value - then these two DEFAULT AUTOINCREMENTs will each increase on their own. And using DEFAULT with the @@IDENTITY special value doesn't work, either, as it would get its value before the ID column is set. (I can't prefix the two at signs to the IDENTIY name, as it automatically build a block quote, FWIW... - EDIT: but now it works, thanks to Reimer!)

)

So using a before trigger might be your solution:

create trigger TI_test
   before insert on "DBA".test2
referencing new as N
for each row
begin
   if N."advancedid" is null then
      set N."advancedid" = N."id";
   end if;
end;

-- some test cases
insert test2 values(default, default);
insert test2 values(default, default);
insert test2 values(default, 1000);
insert test2 values(default, default);
insert test2 values(default, 2000);
insert test2 values(100, 200);
insert test2 values(101, default);
select * from test2;

-- output
1   1
2   2
3   1000
4   4
5   2000
100   200
101   101

[reimer_pods · Accepted solution]

Displaying @@IDENTITY works with preceding backslashes (\\@\\@)

[VolkerBarth · Accepted solution]

It does in a @comment, but (at least for me) it does not work in an answer (which I had tried of course) - the answer editor immediately creates a blockquote once I enter the @sign - no matter if I try to escape it or not:(

[reimer_pods · Accepted solution]

Copying the string "@@IDENTITY" from a lokal editor into the answer text box did the trick for me 😉

[VolkerBarth · Accepted solution]

@Reimer: Thanks for the pointer:)