0

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

Caballero
  • 11,546
  • 22
  • 103
  • 163
  • 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 Mar 16 '14 at 19:54
  • @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 Mar 16 '14 at 20:08

2 Answers2

2

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

Sami Badawi
  • 977
  • 1
  • 10
  • 22
0

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?

cvogt
  • 11,260
  • 30
  • 46
  • I've tried that, the error doesn't change at all. I'm starting to think that this is maybe not possible in postgres at all? – Caballero Mar 17 '14 at 05:59
  • Maybe you need to add the schema to the qualified path. Also see http://www.postgresql.org/docs/9.1/static/ddl-schemas.html#DDL-SCHEMAS-CREATE and http://stackoverflow.com/questions/7942520/relationship-between-catalog-schema-user-and-database-instance – cvogt Mar 17 '14 at 14:59