Hello River Thanks for your suggestion but I need to use UPDATE OR INSERT.
I know that an INSERT without an IDENTITY column will work well. That's not the problem. But I never use INSERT, I always use UPDATE OR INSERT. And with an IDENTITY column it is impossible to use UPDATE OR INSERT therefore the IDENTITY columns are useless for me and I have returned to the old and well fashioned generator/trigger method. I have installed Firebird 2.5.9 and Firebird 3.0.7 because there are dozens of applications using Firebird 2.5 and my new application is using Firebird 3. So far, the only problem I have found with Firebird 3 is that IDENTITY columns can not be used with UPDATE OR INSERT. Greetings. Walter. <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> Libre de virus. www.avast.com <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2> On Wed, Nov 25, 2020 at 6:13 AM 'River~~' [email protected] [firebird-support] <[email protected]> wrote: > > > hi Walter > > Try > > INSERT INTO mytable(column2) VALUES (12345) > > In other words, do not try to insert the column you want to be > auto-generated. The database engine will notice that you are inserting > only a subset of the columns, and SHOULD use the default values for > the missing columns, which would be NULL unless you specified > otherwise. In the case of column1 you have specified that the default > value would be the auto-incremented value. If your table has a > column3 then you should find that is set to NULL by my suggested > syntax. > > I do not have v3 running currently so cannot test it myself, so please > report back for the benefit of myself and everyone else whether that > works for you or not. > > I am also interested to know if you find my syntax works on v2.5, if > you still have it installed to test it? Please don't go to the trouble > of re-installing it just to test it. > > It is arguable that v3 is correct in rejecting the syntax you have > previously used, because it does not make logical sense to tell the > database engine to insert a null when you mean something else. The > syntax I suggested will be more portable to other db engines because > it is closer to the standard. > > I hope that suggestion proves helpful > Warmly > River~~ > > On 19/11/2020, 'Walter R. Ojeda Valiente' > [email protected] [firebird-support] > <[email protected]> wrote: > > Hello everybody > > > > A long, long time without writing neither reading this group, mostly > > because all that I need about Firebird I knew. > > > > But now, I have a doubt. > > > > With Firebird 2.5.x I can have an auto-incremental column and use it in > an > > UPDATE OR INSERT, but such thing is not possible with Firebird 3. > > > > With Firebird 2.5.x a generator and a trigger are created and the value > of > > the column is put automatically. So, if I write: > > UPDATE OR INSERT INTO MyTable (MyColumn1, MyColumn2) VALUES (NULL, > 12345); > > > > and MyColumn1 is auto-incremental its value is set for the Firebird > engine. > > > > but...if I use the new IDENTITY type in Firebird 3 I always have an > error, > > writing MyColumn1 in the UPDATE OR INSERT or not writing. > > > > So, my question is: > > > > Is it possible to use UPDATE OR INSERT with an IDENTITY column? > > > > Thanks in advance. > > > > Greetings. > > > > Walter. > > > > -- > 9831*2^1441403+1 is prime, >400k digits > >
