liquibase unable to create index and unique constraint on partitioned table

Question

Liquibase file is as below :

databaseChangeLog:

  - changeSet:
      id: 1
      author: roran
      changes:
        - createTable:
            tableName: account_balance
            columns:
              - column:
                  name: id
                  type: bigint
                  autoIncrement: true
                  constraints:
                    primaryKey: true
              - column:
                  name: account_id
                  type: bigint
                  constraints:
                    nullable: false
              - column:
                  name: balance_date
                  type: date
                  constraints:
                    primaryKey: true
              - column:
                  name: create_date
                  type: timestamptz
              - column:
                  name: created_by
                  type: varchar(100)
              - column:
                  name: update_date
                  type: timestamptz
              - column:
                  name: updated_by
                  type: varchar(100)
        - modifySql:
            applyToRollback: false
            dbms: postgresql
            append:
              value: PARTITION BY RANGE (balance_date);
        - addUniqueConstraint:
            columnNames:  balance_date, account_id
            tableName:  account_balance
        - createIndex:
            columns:
              - column:
                  name:  account_id
            indexName:  account_id_idx
            tableName:  account_balance

I am getting below error on adding unique constraint while starting spring boot application

Migration failed for change set /db/changelog/changes/1.create-account-balance-table.yml::1::roran: Reason: liquibase.exception.DatabaseException: ERROR: syntax error at or near PARTITION Position: 93 [Failed SQL: (0) ALTER TABLE public.account_balance ADD UNIQUE (balance_date, account_id)PARTITION BY RANGE (balance_date);]\tat org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1794)\tat org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:594)\tat org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:516)\

Similarly, I am getting error on adding index while starting spring boot application.

liquibase.exception.MigrationFailedException: Migration failed for change set db/changelog/changes/1.create-account-balance-table.yml::1::roran: Reason: liquibase.exception.DatabaseException: ERROR: syntax error at or near PARTITION Position: [Failed SQL: (0) CREATE INDEX account_id_idx ON public.account_balance_p0(account_id)PARTITION BY RANGE (balance_date); CREATE TABLE public.account_balance_p0 PARTITION OF account_balance FOR VALUES FROM ('2020-10-01 00:00:00') TO ('2020-11-01 00:00:00');]\tat liquibase.changelog.ChangeSet.execute(ChangeSet.java:646)\tat liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:53)\tat liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:83)\tat liquibase.Liquibase.update(Liquibase.java:202)\tat

Same is working fine , if I create table via liquibase and later programmatically if i create index or add constraint as below

CREATE INDEX account_id_idx ON public.account_balance(account_id);

Is there any error the way I am using yaml in liquibase for partitioned table?

I am using postgresql 11 version and liquibase with spring boot 2.3.3.RELEASE

sudo sudo · Accepted Answer · 2020-10-12T15:39:37

The modifySql Type of your ChangeSet is applied to your addUniqueConstraint and createIndex Types as well. The exception of the generated sql occurs due to the addition of the PARTITION BY RANGE to the Index and UniqueConstraint creation.

Just split it in 2 parts - one ChangeSet with the table creation and one ChangeSet with the Index and UniqueConstraint creation:

  - changeSet:
      id: 1
      author: roran
      changes:
        - createTable:
            tableName: account_balance
            columns:
              ...
        - modifySql:
            applyToRollback: false
            dbms: postgresql
            append:
              value: PARTITION BY RANGE (balance_date);

and

  - changeSet:
      id: 2
      author: roran
      changes:
        - addUniqueConstraint:
            columnNames:  balance_date, account_id
            tableName:  account_balance
        - createIndex:
            columns:
              column:
                name:  account_id
            indexName:  account_id_idx
            tableName:  account_balance

liquibase unable to create index and unique constraint on partitioned table

1 Answers