addPrimaryKey / addUniqueConstraint could support forIndexName on PostgreSQL

[jannic]

Environment

Liquibase Version: 3.10.2

Liquibase Integration & Version: all

Liquibase Extension(s) & Version:

Database Vendor & Version: postgresql >= 9.1

Operating System Type & Version: all

Description

Since version 9.1, PostgreSQL supports the same USING INDEX syntax when creating a unique constraint as Oracle does.

Therefore, the forIndexName could be supported by liquibase for addPrimaryKey and addUniqueConstraint

Steps To Reproduce

        <addPrimaryKey columnNames="some_column" constraintName="some_constraint" tableName="some_table" forIndexName="some_index" />

Actual Behavior

Changeset is rejected on postgresql.

Expected/Desired Behavior

Changeset should be executed.

Additional Context

It's probably enough to add postgresql to the checks at https://github.com/liquibase/liquibase/blob/31a22561423919b3875e0563a7bdcde3b9e457a9/liquibase-core/src/main/java/liquibase/sqlgenerator/core/AddPrimaryKeyGenerator.java#L35 and https://github.com/liquibase/liquibase/blob/d6dcd487fbb56a4a381491802c341f50e74bee59/liquibase-core/src/main/java/liquibase/sqlgenerator/core/AddUniqueConstraintGenerator.java#L33

[molivasdat]

Hi @jannic Thanks for bringing this to our attention and writing up the issue. We will add this to our list of issues to process. Thanks for pointers in the code. Do you think you can submit a PR with a fix?

[jannic]

There is an additional complication I didn't anticipate: While PostgreSQL basically uses the same syntax as Oracle, it doesn't allow for both a list of columns and a USING INDEX ... specification at the same time. This makes sense as with a given index, the list of columns is already fixed. However, AddUniqueConstraintGenerator does always include the list of columns. This fails with PostgreSQL. So there are some more changes needed than mentioned above.

[alexey-semenov]

Hello folks! Will start working on it if no objections.

[ragchuck]

What's the status of this? we want to start using liquibase for Oracle and Postgres-DB but currently our changelogs failing because of this. While Oracle automatically maps the correct Unique Index with the same name, Postgres tries to create it on top of it and fails: Unexpected error running Liquibase: ERROR: relation "%s" already exists [Failed SQL: (0) ALTER TABLE public....

[FBurguer]

More like a TypeEnhancment. We are planning to look more into it on the future.