Question 114989 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – liquibase default schema ignored in sql changelog

Problem: liquibase can’t find table without setting schema in SQL script.

How to say liquibase use default schema in SQL changelog?

Before sql changelog, for adding check constraint, I create all table, without setting schema. Schema was set in application.properties and all table was created correctly in $RM_DB_SCHEMA.

RM_DB_SCHEMA: MANAGER
RM_DB_URL: "jdbc:h2:file:~/rmdb;MODE=PostgreSQL;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;AUTO_SERVER=TRUE;INIT=CREATE SCHEMA IF NOT EXISTS ${RM_DB_SCHEMA}"
RM_DB_USER: sa
RM_DB_PASSWORD: admin
RM_LB_USER: ${RM_DB_USER}
RM_LB_PASSWORD: ${RM_DB_PASSWORD}
spring:
  datasource:
    hikari:
      schema: ${RM_DB_SCHEMA}
      username: ${RM_DB_USER}
      password: ${RM_DB_PASSWORD}
      jdbc-url: ${RM_DB_URL}
  liquibase:
    change-log: "classpath:db/manager-changelog.xml"
    default-schema: ${RM_DB_SCHEMA}
    user: ${RM_LB_USER}
    password: ${RM_LB_PASSWORD}
  jpa:
    database: postgresql

Caused by: liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for change set changelog.xml::d::d:
Reason: liquibase.exception.DatabaseException: Таблица "STATUS" не найдена
Table "STATUS" not found; SQL statement:
ALTER TABLE TEST ADD CONSTRAINT STATUS_ID CHECK (exists (SELECT 1 FROM STATUS s WHERE STATUS_ID = s.id)) [42102-200] [Failed SQL: (42102) ALTER TABLE TEST ADD CONSTRAINT STATUS_ID CHECK (exists (SELECT 1 FROM STATUS s WHERE STATUS_ID = s.id))]

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I found another solution. The problem was in local developing with h2. (it always init as public schema). I'm just adding SET SCHEMA after creating it.

    in test properties:

    jdbc-url: 'jdbc:h2:file:~/rmdb;MODE=PostgreSQL;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;AUTO_SERVER=TRUE;INIT=CREATE SCHEMA IF NOT EXISTS ${application.database.schema};SET SCHEMA ${application.database.schema}'

  2. 0

    One easy one to fix this, is to use the property: database.defaultSchemaName.

    You can write to your sql statement like this:

    <sql>UPDATE ${database.defaultSchemaName}.product SET ...</sql>
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search