Search code examples
hsqldb

Altering a varbinary length in hsqldb

If I have created a table with VARBINARY(16) and later decide I need to store longer binary data, can I safely alter an existing table? Or do I even need to? Internally is hsqldb optimizing the storage of the length and will it need to rewrite all the existing data?

The documentation does not cover this case.

Solution

  • With HSQLDB, all SQL statements for altering the tables are safe. With VARBINARY and VARCHAR, you need to execute the ALTER COLUMN statement to increase the maximum length. For example:

    ALTER TABLE thetable ALTER COLUMN thecolumn SET DATA TYPE VARBINARY(32)

    When increasing the length, the statement executes instantly. When reducing, it will check all the data and will not change the limit if any existing data is longer than the new maximum.

    http://hsqldb.org/doc/guide/databaseobjects-chapt.html#dbc_table_manupulation