CODEWITHSUNDEEP
postgresqlhsqldbliquibase
degath
degath

Reputation: 1621

create unique index with function in hsqldb / liquibase

Is there a way to use function index in hsqldb?

I tried those 4:

<column name="LOWER(name)"/>
<column name="LCASE(name)"/>
<column name="LOWER(name)" computed="true"/>
<column name="LCASE(name)" computed="true"/>

Inside my createIndex changeset:

<changeSet author="dgt" id="unique-postgres" dbms="hsqldb">
    <createIndex indexName="lower_case_index" tableName="users" unique="true">
        <column name="LOWER(name)" computed="true"/>
    </createIndex>

In documentation I noticed that hsqldb got: LOWER and LCASE built in function, but any of those do not work for me.

Every single time I've got an error:

Reason: liquibase.exception.DatabaseException: unexpected token: ( required: ) [Failed SQL: CREATE UNIQUE INDEX PUBLIC.lower_case_index ON PUBLIC.users(LOWER(name))]

I know about a solution that I can change column type from VARCHAR to VARCHAR_IGNORECASE, but it's not a case for me, because I need a solution to work on both db: hsqldb and postgres.

My ideal solution should look like this:

<changeSet author="dgt" id="users-unique-index-postgres" dbms="hsqldb">
    <createIndex indexName="name_index" tableName="users" unique="true">
        <column name="LOWER(name)" computed="true"/>
    </createIndex>
</changeSet>
<changeSet author="dgt" id="users-unique-index-hsqldb" dbms="postgresql">
    <createIndex indexName="name_index" tableName="users" unique="true">
        <column name="lower(name)" computed="true"/>
    </createIndex>
</changeSet>

But it doesn't work.

Upvotes: 0

Views: 2268

Answers (1)

user330315
user330315

Reputation:

HSQLDB does not support function based indexes at all, so you need to find a different solution. You could e.g. define the column as varchar_ignorecase instead of varchar and then create a "normal" unique index on that column.

You can keep a single table definition by using properties.

That could look like this:

<changeSet author="dgt" id="create-users-table">
  <property name="users_name_type" value="varchar" dbms="postgresql"/>
  <property name="users_name_type" value="varchar_ignorecase" dbms="hsqldb"/>

  <createTable tableName="users">
    <column name="name" type="${users_name_type}">
      <constraints nullable="false"/>
    </column>
  </createTable>
</changeSet>


<changeSet author="dgt" id="users-unique-index-postgres" dbms="postgresql">
  <createIndex indexName="name_index" tableName="users" unique="true">
    <column name="lower(name)" computed="true"/>
  </createIndex>
</changeSet>

<changeSet author="dgt" id="users-unique-index-hsqldb" dbms="hsqldb">
  <createIndex indexName="name_index" tableName="users" unique="true">
    <column name="name"/>
  </createIndex>
</changeSet>

Upvotes: 1

Related Questions