Firebird add column with default value but different value

Question

I'm trying to add a column to a firebird table. The column should have a default value 1 but initially for already existing rows the value should be set to 0. And that should happen in one transaction.

I tried

ALTER TABLE MYTABLE ADD MYCOLUMN SMALLINT DEFAULT 1 NOT NULL;
UPDATE MYTABLE SET MYCOLUMN = 0; 

but that's not allowed in one transaction, because the update won't see the new column. I also tried:

ALTER TABLE MYTABLE ADD MYCOLUMN SMALLINT DEFAULT 0 NOT NULL;
ALTER TABLE MYTABLE ALTER COLUMN MYCOLUMN SET DEFAULT 1 NOT NULL;

Hoping that the column will be 0 afterwards but it will be 1.

More options to get what I want in one transaction?

Answer 1

Unfortunately you can't do this in a single transaction. Firebird doesn't allow updates/inserts to use objects created in the same transaction (in this case the column) because DDL is actually only really executed/completed at transaction commit, and for the same reason executing the two alter table statements for the same column will behave as if only a single alter table statement was issued (so the last default will be applied).

You have two options:

1. add the column with the initial default of 0, commit, alter the column with the new default of 1,
2. add the column with the final default of 1, commit, update the column with the initial value of 0.

There are actually some more options, but that would only complicate matters (especially before Firebird 3), and require even more transactions.

As an aside: ALTER TABLE MYTABLE ALTER COLUMN MYCOLUMN SET DEFAULT 1 NOT NULL should have given you a syntax error because in Firebird 2.5 changing NOT NULL is not supported, and in Firebird 3 it requires a separate alter table mytable alter column set not null.