Spring Boot执行SQL脚本报错：PostgreSQL未选择创建Schema

错误详情 #

org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #2 of class path resource [sql/init.sql]: CREATE TABLE IF NOT EXISTS authorities ( username varchar(255) NOT NULL, authority varchar(50) NOT NULL, CONSTRAINT fk_authorities_users FOREIGN KEY(username) REFERENCES users(username) ); nested exception is org.postgresql.util.PSQLException: ERROR: no schema has been selected to create in
  Position: 28

相关代码文件 #

init.sql #

BEGIN TRANSACTION;

CREATE TABLE IF NOT EXISTS authorities (
    username varchar(255) NOT NULL,
    authority varchar(50) NOT NULL,
    CONSTRAINT fk_authorities_users
        FOREIGN KEY(username)
            REFERENCES users(username)
);

CREATE SEQUENCE IF NOT EXISTS sequence_username_id START WITH 500000000;
ALTER TABLE users ALTER COLUMN attempts DROP NOT NULL;
ALTER TABLE users ALTER COLUMN usernameid SET NOT NULL;
ALTER TABLE users ALTER COLUMN usernameid SET DEFAULT nextval('sequence_username_id'::regclass);
ALTER TABLE users ALTER COLUMN externalthirdpartyuserflag SET DEFAULT false;
ALTER TABLE users ALTER COLUMN passwordtemporaryflag SET DEFAULT false;
ALTER TABLE users ALTER COLUMN activated SET DEFAULT false;

COMMIT TRANSACTION;

BaseIntegrationTest.java #

...
@Value("${dynamic-schema}")
private String dynamicSchema;

protected void executeSqlFile(String sqlFileName) {
  jdbcTemplate.execute("SET search_path TO \"" + dynamicSchema + "\"");
  jdbcTemplate.execute("grant usage on schema public to public");
  jdbcTemplate.execute("grant create on schema public to public");
  Resource resource = new ClassPathResource(sqlFileName);
  ResourceDatabasePopulator databasePopulator = new ResourceDatabasePopulator(resource);
  databasePopulator.execute(jdbcTemplate.getDataSource());
}

application.properties #

hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
hibernate.hbm2dll.create_namespaces=true
hibernate.default_schema=${dynamic-schema}
...
dataSource.url=jdbc:postgresql://localhost:5432/${dynamic-database}?currentSchema=${dynamic-schema}
dataSource.driverClassName=org.postgresql.Driver
...
dataSource.hikari.jdbcUrl=jdbc:postgresql://localhost:5432/postgres?currentSchema=${dynamic-schema}
dataSource.hikari.username=postgres
dataSource.hikari.password=postgres
dataSource.hikari.autoCommit=false
datasource.hikari.schema=${dynamic-schema}
...
dynamic-schema=MySchema

AuthDataSourceConfiguration.java #

...
private Properties getJpaProperties(HibernateProperties hibernateProperties) {
  Properties jpaProperties = new Properties();
  ...
  jpaProperties.put("javax.persistence.create-database-schemas", true);

  return jpaProperties;
}

问题原因 #

1. ResourceDatabasePopulator使用独立连接：通过jdbcTemplate设置的search_path仅作用于当前JDBC连接，但ResourceDatabasePopulator.execute()会从数据源获取新连接，之前的search_path设置不会生效，导致脚本执行时无指定Schema。
2. Hikari配置项拼写错误：application.properties中datasource.hikari.schema为小写开头，HikariCP的正确配置项是dataSource.hikari.schema（大写D开头），该配置未被正确读取，连接默认Schema未设置。
3. SQL脚本未显式指定Schema：脚本中CREATE TABLE等语句未指定目标Schema，当连接默认Schema未正确配置时，PostgreSQL无法确定对象创建位置。
4. Schema可能未提前创建：Hibernate的Schema创建配置（create_namespaces等）是在实体映射阶段执行，若SQL脚本执行时机早于Hibernate初始化，自定义Schema可能尚未创建。

解决办法 #

方法1：为ResourceDatabasePopulator添加连接初始化语句 #

确保执行脚本的连接自动设置search_path：

protected void executeSqlFile(String sqlFileName) {
  Resource resource = new ClassPathResource(sqlFileName);
  ResourceDatabasePopulator databasePopulator = new ResourceDatabasePopulator();
  // 添加Schema初始化语句
  databasePopulator.addScript(new ByteArrayResource(("SET search_path TO \"" + dynamicSchema + "\";").getBytes()));
  databasePopulator.addScript(resource);
  databasePopulator.execute(jdbcTemplate.getDataSource());
}

方法2：修正HikariCP配置项 #

修复配置拼写错误，让连接默认使用自定义Schema：

# 替换原错误配置
# datasource.hikari.schema=${dynamic-schema}
dataSource.hikari.schema=${dynamic-schema}

方法3：在SQL脚本中显式指定Schema #

直接在SQL语句中绑定目标Schema，无需依赖连接默认配置：

BEGIN TRANSACTION;

CREATE TABLE IF NOT EXISTS "MySchema".authorities (
    username varchar(255) NOT NULL,
    authority varchar(50) NOT NULL,
    CONSTRAINT fk_authorities_users
        FOREIGN KEY(username)
            REFERENCES "MySchema".users(username)
);

CREATE SEQUENCE IF NOT EXISTS "MySchema".sequence_username_id START WITH 500000000;
ALTER TABLE "MySchema".users ALTER COLUMN attempts DROP NOT NULL;
ALTER TABLE "MySchema".users ALTER COLUMN usernameid SET NOT NULL;
ALTER TABLE "MySchema".users ALTER COLUMN usernameid SET DEFAULT nextval('"MySchema".sequence_username_id'::regclass);
ALTER TABLE "MySchema".users ALTER COLUMN externalthirdpartyuserflag SET DEFAULT false;
ALTER TABLE "MySchema".users ALTER COLUMN passwordtemporaryflag SET DEFAULT false;
ALTER TABLE "MySchema".users ALTER COLUMN activated SET DEFAULT false;

COMMIT TRANSACTION;

若Schema为动态参数，可在脚本中使用占位符，执行前替换为实际名称。

方法4：提前创建自定义Schema #

在执行脚本前先确保Schema存在：

protected void executeSqlFile(String sqlFileName) {
  // 先创建Schema（不存在则创建）
  jdbcTemplate.execute("CREATE SCHEMA IF NOT EXISTS \"" + dynamicSchema + "\"");
  // 执行脚本
  Resource resource = new ClassPathResource(sqlFileName);
  ResourceDatabasePopulator databasePopulator = new ResourceDatabasePopulator();
  databasePopulator.addScript(new ByteArrayResource(("SET search_path TO \"" + dynamicSchema + "\";").getBytes()));
  databasePopulator.addScript(resource);
  databasePopulator.execute(jdbcTemplate.getDataSource());
}

内容的提问来源于stack exchange，提问作者Justin Xu