Liquibase create indexes with functions

Question

I have this database in postgresql which has this index:

CREATE INDEX fav_alias_lower_index ON fav_alias USING hash((LOWER(fav_alias)));

When I reversed engineered this database I got this liquibase changelog:

<changeSet author="jmartins (generated)" id="1410448831080-17">
   <createIndex indexName="workspace_favorite_alias_lower_case_index" tableName="workspace_favorite_alias" unique="false">
      <column name="lower((fava_alias)::text)"/>
   </createIndex>
</changeSet>

This works fine when I apply an update on a PostgreSql database, however, it doesn't work on MySql for example. So my question is how can I make this index creation available for MySql, MSSQL and Oracle too, preserving the lowercasing of fava_alias?

Thanks

Answer 1

If the function is available in your Database, you can use it in liquibase. There is a way to differentiate between different DBs using dbms attribute.

<changeSet author="jmartins (generated)" id="1410448831080-17">
   <createIndex indexName="workspace_favorite_alias_lower_case_index" tableName="workspace_favorite_alias" unique="false" dbms="postgresql">
      <column name="lower((fava_alias)::text)"/>
   </createIndex>
</changeSet>

<changeSet author="jmartins (generated)" id="1410448831080-17">
   <createIndex indexName="workspace_favorite_alias_lower_case_index" tableName="workspace_favorite_alias" unique="false" dbms="oracle">
      <column name="lower(fava_alias)"/>
   </createIndex>
</changeSet>