0
votes

So I have a Scala/Play application that uses multiple MySQL and PostgreSQL databases (on the same server), however I have a problem with PostgreSQL.

This is the configuration:

db.postgres.driver=org.postgresql.Driver
db.postgres.url="jdbc:postgresql://localhost:5432/"
db.postgres.user=root
db.postgres.password=root

db.mysql.driver="com.mysql.jdbc.Driver"
db.mysql.url="jdbc:mysql://localhost"
db.mysql.user=root
db.mysql.pass=root

This simple method works fine with MySQL:

def test = DB("mysql").withDynSession {
    val rez = sql"""select * from dbName.test""".as[TestRow].list
    println(rez)
}

But exactly the same method with Postgres

def test = DB("postgres").withDynSession {
    val rez = sql"""select * from dbName.test""".as[TestRow].list
    println(rez)
}

throws an error:

org.postgresql.util.PSQLException: ERROR: relation "dbName.test" does not exist

How come these methods are identical, but MySQL example works and PostgreSQL doesn't?

My stack:

  • Scala 2.10.3
  • Play 2.2.2
  • Slick 2.0
  • PostgreSQL 9.3.3
  • MySQL 5.6.16
  • Java 8

Postgresql JDBC driver version is 9.3-1101-jdbc41

2
Have you verified that running that query through psql returns results correctly? Perhaps you are supplying invalid syntax, or the schema you expect really doesn't exist? - Jason Pearson
@JasonPearson I'm starting to realize that this approach may not be possible in Postgres afterall, as in you can only connect to one particular database, so you can't prefix tables with database name. I may be wrong, but from what I've found so far it's pointing to this conclusion. - Caballero

2 Answers

2
votes

I had the same problem.

I am using Slick 2.0 with lifted embedding and PostgreSQL 9.3.

You have to set your schema when you define your table:

class Coffees(tag: Tag) extends Table[(String, Int, Double, Int, Int)](tag, Some("MYSCHEMA"), "COFFEES") {

Documentation: http://slick.typesafe.com/doc/2.0.1/schemas.html

0
votes

This may be a case sensitivity issue. Try quoting the names and adding the schema to the qualified name: sql"""select * from "dbName"."theSchema"."test"""" Does this help?