Convert column domain as smallint to boolean in firebird

June 22, 2022

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:

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

>Solution :

Unfortunately, this is an incompatible column change, because there is no conversion defined from SMALLINT to 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 BOOLEAN).

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 BOOLEAN moving 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.