How change a column type in Firebird 3

Question

Since Firebird 3, I can't modify a column type.

Before I use this kind of update:

update RDB$RELATION_FIELDS set
RDB$FIELD_SOURCE = 'MYTEXT'
where (RDB$FIELD_NAME = 'JXML') and
(RDB$RELATION_NAME = 'XMLTABLE')

because I get ISC error 335545030 ("UPDATE operation is not allowed for system table RDB$RELATION_FIELDS").

Maybe there is another way in Firebird 3?

Undercool

Answer 1

Firebird 3 no longer allows direct updates to the system tables, as that was a way to potentially corrupt a database. See also System Tables are Now Read-only in the release notes. You will need to use DDL statements to do the modification.

It looks like you want to change the data type of a column to a domain. You will need to use alter table ... alter column ... for that. Specifically you will need to do:

alter table XMLTABLE
    alter column JXML type MYTEXT;

This does come with some restrictions:

Changing the Data Type of a Column: the TYPE Keyword

The keyword TYPE changes the data type of an existing column to another, allowable type. A type change that might result in data loss will be disallowed. As an example, the number of characters in the new type for a CHAR or VARCHAR column cannot be smaller than the existing specification for it.

If the column was declared as an array, no change to its type or its number of dimensions is permitted.

The data type of a column that is involved in a foreign key, primary key or unique constraint cannot be changed at all.

This statement has been available since before Firebird 1 (InterBase 6.0).

Envelop

For blob is not allowed, what is the best method to change charset on blob text column for exemple ? – Undercool

@Undercool The only real way is to add a new column and update to assign the value of the old column to the new column (with appropriate casts where necessary), drop the old column and rename the new column. – Envelop

@Undercool suddenly it was said BLOB's are risk of data loss here: tracker.firebirdsql.org/browse/CORE-6052 – Yuma

Answer 2

Firebird 2.5 manual, chapter Data Definition (DDL) Statement, section TABLE:

 ALTER TABLE tabname ALTER COLUMN colname TYPE typename

Strobel

In Firebird 2.5 there is some thing you can't do. For example changing not null property... In Firebird 3.0 it's Ok – Twain

@HuguesVanLandeghem it is true both ways. If you type your columns using named DOMAINs then in FB2 you can change the field type from one domain to another by directly modifying a system table. In FB3 system tables were made read-only. However they still do not offer SQL for changing away from VarChar domain to another in a column definition. I do not remember exactly now, whether the destination domain was a longer VarChar or a BLOB SUB_TYPE TEXT, but it just did not work – Yuma