Scala Slick插入数据报错：relation 'movies.Movie'不存在

调用demoInsertMovie()方法时，Future执行失败，抛出错误：

org.postgresql.util.PSQLException: ERROR: relation "movies.Movie" does not exist

数据库已通过初始化脚本创建目标表，且能正常查询到表结构。

问题原因与修复方案 #

核心原因 #

PostgreSQL中带双引号的表名区分大小写。初始化脚本创建的表为movies."Movie"（首字母大写），但Slick默认会将表名转为小写，生成的SQL尝试访问movies.movie，导致找不到对应表。

修复步骤 #

方案一：统一使用小写表名（推荐）

1. 修改初始化脚本，将表名改为小写：

   create table if not exists movies.movie ("movie_id" BIGSERIAL NOT NULL PRIMARY KEY,"name" VARCHAR NOT NULL,"release_date" DATE NOT NULL,"length_in_min" INTEGER NOT NULL);
   -- 其他表名同步改为小写
   

2. 修改Slick表定义，匹配小写表名：

   class MovieTable(tag: Tag) extends Table[Movie](tag, Some("movies"), "movie") {
     // 字段定义保持不变
   }
   

方案二：保留大写表名，让Slick生成带引号的SQL

修改MovieTable类，覆盖tableName方法强制生成带引号的表名：

class MovieTable(tag: Tag) extends Table[Movie](tag, Some("movies"), "Movie") {
  override def tableName = "\"Movie\""
  // 字段定义保持不变
}

额外优化建议

• 在数据库连接配置中指定默认schema，避免每次查询都要带schema前缀：
  在application.conf的postgres配置里添加：

  properties = {
    // 原有配置...
    currentSchema = "movies"
  }
  

• 开启Slick日志验证生成的SQL：
  在application.conf中添加：

  logger.scala.slick = DEBUG
  

相关代码与环境信息 #

Main.scala #

package com.slickdb
import java.time.LocalDate
import scala.concurrent.{ExecutionContext, Future}
import java.util.concurrent.{ExecutorService, Executors}
import scala.util.{Failure, Success}


object PrivateExecutionContext {
  val executor: ExecutorService = Executors.newFixedThreadPool(4)
  implicit val ec: ExecutionContext = ExecutionContext.fromExecutorService(executor)
}


object Main {

  import PrivateExecutionContext._
  import slick.jdbc.PostgresProfile.api._
    
  val shawshankRedemption: Movie = Movie(1L, "The shawshank Redemption", 
                                   LocalDate.of(1994, 9, 23), 162)

  def demoInsertMovie(): Unit = {
    val queryDescription = SlickTables.movieTable += shawshankRedemption

    val futureId: Future[Int] = Connection.db.run(queryDescription)

    futureId.onComplete {
      case Success(newMovieId) => 
             println(s"Query was successful, new movie id is $newMovieId")
      case Failure(ex) => 
             println(s"Query failed, reason: $ex")
    }

    Thread.sleep(10000)
  }

  def main(args: Array[String]): Unit = {
    demoInsertMovie()
  }
}

Connection.scala #

package com.slickdb

import slick.jdbc.PostgresProfile.api._

object Connection {
  val db = Database.forConfig("postgres")
}

Model.scala #

package com.slickdb

import java.time.LocalDate

case class Movie(id: Long, name: String, releaseDate: LocalDate, lengthInMin: Int)

object SlickTables {
  import slick.jdbc.PostgresProfile.api._

  class MovieTable(tag: Tag) extends Table[Movie](tag, Some("movies"), "Movie") {
    def id = column[Long]("movie_id", O.PrimaryKey, O.AutoInc)
    def name = column[String]("name")
    def releaseDate = column[LocalDate]("release_date")
    def lengthInMin = column[Int]("length_in_min")

    
    override def * = (id, name, releaseDate, lengthInMin) <> (Movie.tupled, Movie.unapply)
  }

  lazy val movieTable = TableQuery[MovieTable]
}

application.conf #

postgres = {
  connectionPool = "HikariCP"
  dataSourceClass = "org.postgresql.ds.PGSimpleDataSource"
  properties = {
    serverName = "localhost"
    portNumber = "5432"
    databaseName = "postgres"
    user = "postgres"
    password = "wadefff"
  }
  numThreads = 10
}

init-scripts.sql（路径：ThisProject/db/init-scripts.sql） #

create extension hstore;
create schema movies;
create table if not exists movies."Movie" ("movie_id" BIGSERIAL NOT NULL PRIMARY KEY,"name" VARCHAR NOT NULL,"release_date" DATE NOT NULL,"length_in_min" INTEGER NOT NULL);
create table if not exists movies."Actor" ("actor_id" BIGSERIAL NOT NULL PRIMARY KEY,"name" VARCHAR NOT NULL);
create table if not exists movies."MovieActorMapping" ("movie_actor_id" BIGSERIAL NOT NULL PRIMARY KEY,"movie_id" BIGINT NOT NULL,"actor_id" BIGINT NOT NULL);
create table if not exists movies."StreamingProviderMapping" ("id" BIGSERIAL NOT NULL PRIMARY KEY,"movie_id" BIGINT NOT NULL,"streaming_provider" VARCHAR NOT NULL);
create table if not exists movies."MovieLocations" ("movie_location_id" BIGSERIAL NOT NULL PRIMARY KEY,"movie_id" BIGINT NOT NULL,"locations" text [] NOT NULL);
create table if not exists movies."MovieProperties" ("id" bigserial NOT NULL PRIMARY KEY,"movie_id" BIGINT NOT NULL,"properties" hstore NOT NULL);
create table if not exists movies."ActorDetails" ("id" bigserial NOT NULL PRIMARY KEY,"actor_id" BIGINT NOT NULL,"personal_info" jsonb NOT NULL);

build.sbt #

ThisBuild / version := "0.1.0-SNAPSHOT"

ThisBuild / scalaVersion := "2.13.8"

lazy val root = (project in file("."))
  .settings(
    name := "slick-demo-live"
  )

libraryDependencies ++= Seq(
  "com.typesafe.slick" %% "slick" % "3.3.3",
  "org.postgresql" % "postgresql" % "42.3.4",
  "com.typesafe.slick" %% "slick-hikaricp" % "3.3.3",
  "com.github.tminglei" %% "slick-pg" % "0.20.3",
  "com.github.tminglei" %% "slick-pg_play-json" % "0.20.3"
)

docker-compose.yml（路径：ThisProject/docker-compose.yml） #

version: '3.8'
services:
  db:
    image: postgres
    restart: always
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=wadefff
    ports:
      - '5432:5432'
    volumes:
      - db:/var/lib/postgresql15/data
      - ./db/init-scripts.sql:/docker-entrypoint-initdb.d/scripts.sql

volumes:
  db:
    driver: local

docker-compose up执行结果 #

| /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/scripts.sql
db_1  | CREATE EXTENSION
db_1  | CREATE SCHEMA
db_1  | CREATE TABLE
db_1  | CREATE TABLE
db_1  | CREATE TABLE
db_1  | CREATE TABLE
db_1  | CREATE TABLE
db_1  | CREATE TABLE
db_1  | CREATE TABLE

数据库查询验证 #

>docker exec -it slick-demo-live_db_1 psql -U postgres

postgres=# select * from movies."Movie";
 movie_id | name | release_date | length_in_min
----------+------+--------------+---------------
(0 rows)

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