Search code examples
sqlhsqldb

HSQLDB add column to table if not exists


Trying to add column column1 to table1 if it doesn't exist yet:

CREATE PROCEDURE column_present()
        MODIFIES SQL DATA
    BEGIN ATOMIC
        DECLARE column_count integer;
        set column_count = select COUNT(*) from information_schema.system_columns Where table_name = 'table1' and column_name = 'column1';
        if column_count = 0 then alter table table1 ADD column1 integer; end if;
    END;

results in:

[2019-05-03 22:28:13] [42581][-5581] unexpected token: ALTER : line: 6
[2019-05-03 22:28:13] java.lang.RuntimeException: org.hsqldb.HsqlException: unexpected token: ALTER : line: 6
[2019-05-03 22:28:13]   at org.hsqldb.error.Error.parseError(Unknown Source)

what is the proper way to create the column in existing table (if it doesn't exist yet) in the HSQLDB?

Please note: ignoring the error on creation if it already exists, is not an option for me.


Solution

  • got answer on the hsqldb mailing list (https://sourceforge.net/p/hsqldb/mailman/message/36657341/):

    Although we can add IF NOT EXISTS to most database object creation statements, this is not possible for ALTER statements.

    It is also not possible to use DDL statements inside a PROCEDURE.

    We may add one of these capabilities to the next version, 2.5.0, in the coming days.

    So currently it's obviously not possible.