2

In my database I have several fields with INTEGER Type. I need to change some of them to BIGINT.

So my question is, can I just use the following command?

ALTER TABLE MyTable ALTER COLUMN MyIntegerColumn TYPE BIGINT;

Are the contained data be converted the correct way? After the convert is this column a "real" BIGINT column?

I know this is not possible if there are constraints on this column (Trigger, ForeingKey,...). But if there are no constraints is it possible to do it this way?

Or is it better to convert it by a Help-Column:

MyIntegerColumn -> MyIntegerColumnBac -> MyBigIntColumn
Andreas
  • 306
  • 3
  • 14
  • If one or more of those columns have no constraints against them (like a foreign key, index, default, rule, etc) then ansвer is Yes,the data will be converted corectly.(This for Firebird 2.5 For Firebird 3.0 I don't know). – Val Marinov Mar 15 '17 at 09:18
  • Thanks. Because I don't want to run into problems in future which I don't know yet. – Andreas Mar 15 '17 at 09:44
  • According to https://firebirdsql.org/refdocs/langrefupd25-ddl-table.html#langrefupd25-alter-table triggers and stored procedures do not prevent changing a columns type since Firebird 2.5. – René Hoffmann Mar 15 '17 at 12:10

1 Answers1

8

When you execute

ALTER TABLE MyTable ALTER COLUMN MyIntegerColumn TYPE BIGINT;

Firebird will not convert existing data from INTEGER to BIGINT, instead it will create a new format version for the table.

When inserting new rows or updating existing rows, the value will be stored as a BIGINT, but when reading Firebird will convert 'old' rows on the fly from INTEGER to BIGINT. This happens transparently for you as the user. This is to prevent needing to rewrite all existing rows, which could be costly (IO, garbage collection of old versions of rows, etc).

So please, do use ALTER TABLE .. ALTER COLUMN, do not do MyIntegerColumn -> MyIntegerColumnBac -> MyBigIntColumn. There are some exceptions to this rule, eg (potentially) lossy character set transformations are better done that way to prevent transliterations errors on select if a character does not exist in the new character set, or changing a (var)char column to be shorter (which can't be done with alter column).

To be a little more specific: when a row is written in the database it contains a format version (aka version count) of that row. The format version points to a description of a row (datatypes, etc) how Firebird should read that row. An alter table will create a new format version, and that format will be applied when writing new rows or updating existing rows. When reading an old row, Firebird will apply necessary transformation to present that row as the new format (for example adding new columns with their default values, transforming a data type of a column).

These format versions are also a reason why the number of alter tables are restricted: if you apply more than 255 alter tables on a single table you must backup and restore the database (the format version is a single byte) before further changes are allowed to that table.

Mark Rotteveel
  • 82,132
  • 136
  • 114
  • 158
  • Thanks for that. So I am going to use "ALTER TABLE MyTable ALTER COLUMN MyIntegerColumn TYPE BIGINT;". I didn't know about the limit of 255 alter-statements! Very good to know! Thanks. Is there a way to read the current "version count" of a table? – Andreas Mar 15 '17 at 14:26