Search code examples
javasqlhsqldb

How to change a column name in HSQLDB when the existing name is a reserved word?

I'm trying to upgrade our DB from HSQLDB 1.7 to the latest version. Unfortunately someone has named a column "Default". I can't seem to rename this column in SQL (this in 1.7 as the rename has to occur before the upgrade).

I have tried escaping the column: 

stmt.executeUpdate("ALTER TABLE table_name ALTER COLUMN \"DEFAULT\" RENAME TO new_name");

and various variations (including '\" and []) and nothing seems to work, I always get back the following

java.sql.SQLException: Column not found: DEFAULT in statement [ALTER TABLE table_name ALTER COLUMN "DEFAULT"]

I'm clearly missing something?

Solution

  • Unfortunately someone has named a column "Default"

    The trouble seems to be the casing. Not only do double quotes "escape" your names, they also make them case-sensitive, unlike non-quoted names, which are case-insensitive in most databases. So you have to use the exact casing when renaming the column:

    ALTER TABLE table_name ALTER COLUMN "Default" RENAME TO new_name

    ... or with JDBC:

    stmt.executeUpdate("ALTER TABLE table_name ALTER COLUMN \"Default\" RENAME TO new_name");