Convert column domain as smallint to boolean in firebird

Question

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

Answer 1

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:

1. Keep existing columns as-is, and only use BOOLEAN moving forward for new columns

2. 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.

The background of this limitation is that Firebird doesn't actually modify existing values when changing the type of a column. Instead, it will convert values on the fly when reading rows created with an older "format version" (inserts and updates will write the new "format version").

This makes for fast DDL, but all conversions must be known to succeed. This basically means only "widening" conversions between similar types are allowed (e.g. longer (VAR)CHAR, longer integer types, etc).