I’m using Firebird 4.0 and I would convert a column from smallint 0|1 to boolean.
So I have this kind of domain:
CREATE DOMAIN D_BOOL AS SMALLINT DEFAULT 0 NOT NULL CHECK (VALUE IN (0,1)) ;
This domain is used in my test table:
CREATE TABLE TBOOL ( ID INTEGER, INTVAL D_BOOL );
How can I convert the column INTVAL to BOOLEAN?
I tried this query but I got an error:
alter table tbool alter column INTVAL TYPE BOOLEAN, alter column INTVAL SET DEFAULT FALSE
Error: *** IBPP::SQLException *** Context: Statement::Execute( alter table tbool alter column INTVAL TYPE BOOLEAN, alter column INTVAL SET DEFAULT FALSE ) Message: isc_dsql_execute2 failed SQL Message : -607 This operation is not defined for system tables. Engine Code : 335544351 Engine Message : unsuccessful metadata update ALTER TABLE TBOOL failed MODIFY RDB$RELATION_FIELDS failed
Unfortunately, this is an incompatible column change, because there is no conversion defined from
BOOLEAN. Altering the type of a column only works for a limited combination of types (and there is no combination that allows modification to or from
The only real option is to add a new column, populate it based on the value of the old column, drop the old column and rename the new column. This can have a huge impact if this column is used in triggers, procedures and/or views.
Your options are basically:
Keep existing columns as-is, and only use
BOOLEANmoving forward for new columns
Do a very invasive change to change all your columns.
If you have a lot of columns that need to change, this is likely easier to do by creating a new database from scratch and pumping the data over, than by changing the database in-place.