Liquibase and functional index-CodePudding

If you try to execute a script to create a functional index on the H2 (PostgreSQL-oriented context) when using Liquibase, a suspicious error occurs.

The script itself looks like this:

    <changeSet id="2" author="...">
        <createIndex tableName="test_table" indexName="test_table_id_idx">
            <column name="(date_trunc('day', create_date))"/>
        </createIndex>
    </changeSet>

Error message:

Syntax error in SQL statement "CREATE INDEX TEST_TABLE_ID_IDX ON TEST_TABLE(([*]DATE_TRUNC('day', CREATE_DATE)))"; expected "identifier"; 
SQL statement: CREATE INDEX test_table_id_idx ON test_table((date_trunc('day', create_date))) [42001-200]

PoC on GitHub

To be honest, I do not have ideas from where such a strange supplement of the script appears (about this [*]). Perhaps H2 does not understand which field you need to use and substitutes Vilcard or something like that.

Yes, I know about possible collisions and differences in the implementation of H2 and the real PostgreSQL (if that, on the PostgreSQL script is performed correctly), but I would like to understand what causes the generation of such a change of the script.

CodePudding user response：

[*] in error messages from H2 marks a position where a syntax error was found. H2 does't support indexes on expressions, you can index only columns.

H2 supports indexes on computed columns, so it is possible to add a computed column with your function, index it, and use this computed column in queries instead of function on base column, in that case this index can be used. But I think it isn't your intention if your primary database system is PostgreSQL. If you use H2 only for some tests, it is more reasonable to remove creation of such incompatible indexes from changesets for H2.