CODEWITHSUNDEEP
javapostgresqloracle-databaseliquibasedatabase-indexes
Piyush Bali
Piyush Bali

Reputation: 125

Database native function while creating index with Liquibase

I am working on a changelog that supports PostgreSQL, Oracle, and MSSQL. While creating a unique index, I need to use the LOWER() function for case-insensitivity. Below are the changesets I currently have to achieve this for PostgreSQL and Oracle:

PostgreSQL:

<changeSet author="abc(generated)" id="1733309361372-323" dbms="postgresql">
        <createIndex indexName="INX_USERS_LOGIN_ID" tableName="USERS" unique="true">
            <column computed="true" name="lower((&quot;LOGIN_ID&quot;)::text)"/>
        </createIndex>
</changeSet>

Oracle:

<changeSet author="abc(generated)" id="1733309361372-323" dbms="oracle">
        <createIndex indexName="INX_USERS_LOGIN_ID" tableName="USERS" unique="true">
            <column computed="true" name="lower(&quot;LOGIN_ID&quot;)"/>
        </createIndex>
</changeSet>

Currently, I have to manage two separate changesets to achieve the same result in both databases (PostgreSQL and Oracle). Is there a way to create a single changeset that works for both PostgreSQL and Oracle, ideally using a more universal solution?

I’m looking for a solution that would be database-agnostic, or at least work for PostgreSQL and Oracle together.

Upvotes: 2

Views: 53

Answers (1)

Pete Pickerill
Pete Pickerill

Reputation: 31

Have you considered using Liquibase's property substitution capability? While there is a bit of additional configuration to consider, you will be able to achieve your goal of only having one change set to represent the change for all database types.

https://docs.liquibase.com/concepts/changelogs/property-substitution.html

It will allow you to have one changeset with a key embedded in the name field and a value defined elsewhere that can be substituted at runtime. Using the example you gave that might look like what's below.

<!-- Name value for Postgres -->
<property name="indexed.column" value="lower((&quot;LOGIN_ID&quot;)::text)" dbms="postgresql"/>
<!-- Name value for Oracle -->
<property name="indexed.column" value="lower(&quot;LOGIN_ID&quot;)" dbms="oracle"/>

<!-- Remove dbms attribute from the changeset element so it will always run -->
<changeSet author="abc(generated)" id="1733309361372-323">
        <createIndex indexName="INX_USERS_LOGIN_ID" tableName="USERS" unique="true">
            <!-- Formatted key in name attribute to trigger substitution -->
            <column computed="true" name="${indexed.column}"/>
        </createIndex>
</changeSet>

To be clear, I didn't run the changesets above to test them so they may need to be tweaked.

Upvotes: 2

Related Questions