create partial index with liquibase

Question

How to create Partial indexes on Liquibase. I want to add a condition in order to create an index but that applies to a subset of the data.

--------------
| Col1 | Col2 |
-------------
| 12  |  1   | Ok
| 12  |  1   | X
| 12  |  2   | Ok
| 12  |  2   | Ok
| 12  |  2   | Ok
-------------

In this scenario, I do not want duplicated items but only for the Rows that match with Col2=1 and some value for the Col1. Though I want to allow duplicated values for the other Col2 values.

Answer 1

Since Liquibase does not have this implementation yet natively, we can include it in this way. Also, it depends if the database supports partial indexes.

<changeSet  author="some_author" id="someId"  context="some_context" dbms="some_db_key">
    <preConditions onFail="MARK_RAN">

        <columnExists tableName="some_table_name" columnName="Col1"/>

        <columnExists tableName="some_table_name" columnName="Col2"/>

    </preConditions>

    <sql splitStatements="true" stripComments="true" endDelimiter="\nGO">
        CREATE UNIQUE INDEX idx_unique_colName1_colName2 ON some_table_name (Col1, Col2)
        WHERE Col2 = '1'
    </sql>
</changeSet>