Question Re: How to create an optional default value? in Technology Q&A

How to create an optional default value?

MCMartin — Mon, 29 Aug 2011 04:39:29 GMT

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.

Re: How to create an optional default value?

VolkerBarth — Mon, 29 Aug 2011 06:22:27 GMT

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

Re: How to create an optional default value?

reimer_pods — Tue, 30 Aug 2011 08:34:50 GMT

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

Re: How to create an optional default value?

VolkerBarth — Tue, 30 Aug 2011 09:35:06 GMT

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:(

Re: How to create an optional default value?

reimer_pods — Wed, 31 Aug 2011 09:14:15 GMT

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

Re: How to create an optional default value?

VolkerBarth — Thu, 01 Sep 2011 03:49:07 GMT

@Reimer: Thanks for the pointer:)