Change database from MS SQL server to postgreSQL for the existing composite application (V2.1.1), index already exists

Support
#1

Hi
I have a composite application where MS SQL Server is used as a database. Now I have to run another instance of the application PostgreSQL instead. I am creating the database from the studio instead of migrating the SQL server database. The empty database is created as expected but there are several index scripts generated by the studio for which I get the following exception “already exists”. How can this be managed?

Caused by: liquibase.exception.DatabaseException: ERROR: relation "idx_hrallowancerate_calcmethod" already exists [Failed SQL: (0) CREATE INDEX IDX_HRALLOWANCERATE_CALCMETHOD ON public.HR_ALLOWANCE_RATE_PAYROLL_GROU(CALC_METHOD_ID)]
	at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:445)
	at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:77)
	at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:160)
	at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1270)
	at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1252)
	at liquibase.changelog.ChangeSet.execute(ChangeSet.java:702)
	... 51 more
Caused by: org.postgresql.util.PSQLException: ERROR: relation "idx_hrallowancerate_calcmethod" already exists
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:335)
	at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:321)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:297)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:292)
	at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:441)
	... 56 more

Platform 2.1.1
Studio: 2.1.999
PostgreSQL database: v16

Looks like database scripts are not created properly, how can I fix this?

#2

Anyone has this experience or can help?

#3

If there are not many such cases, you can edit/delete the changelog manually, that’s what I did when I had problems like this.
Otherwise, I would play with the menu options on the datastore to see if I can do something about it.
image

Kind regards,
Mladen

#4

Hi Mladen
Thanks for trying to help, appreciate it. In fact i deleted the lines but it is regenerated again and again.

#5

Here is the database update script the studio has created:

<changeSet id="1" author="main">
        <createIndex indexName="IDX_HRALLOWANCERATE_CALCMETHOD" tableName="HR_ALLOWANCE_RATE_PAYROLL_GROU">
            <column name="CALC_METHOD_ID"/>
        </createIndex>

        <addForeignKeyConstraint baseColumnNames="CALC_METHOD_ID" baseTableName="HR_ALLOWANCE_RATE_PAYROLL_GROU"
                                 constraintName="FK_HR_ALLOWANCE_RATE_PAYROLL_GROU_ON_CALC_METHOD"
                                 referencedColumnNames="ID" referencedTableName="HMD_CALC_METHOD"/>
    </changeSet>

Here is the error message:

com/inteacc/main/liquibase/changelog/2024/02/22-230229-6eb6a1bd.xml::1::main:
     Reason: liquibase.exception.DatabaseException: ERROR: relation "idx_hrallowancerate_calcmethod" already exists [Failed SQL: (0) CREATE INDEX IDX_HRALLOWANCERATE_CALCMETHOD ON public.HR_ALLOWANCE_RATE_PAYROLL_GROU(CALC_METHOD_ID)]
	at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:136)

However, I checked the database, I don’t see any such index exists.

image

Thnks anyone who can help,