Postgres connectivity via JDBC: org.postgresql.util.PSQLException: ERROR: relation "prescriptions" does not exist

Question

I'm having a ton of trouble connecting to a dockerized postgres instance. The error I get looks like this:

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: relation "prescriptions" does not exist
  Position: 29
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2284)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2003)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:200)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:424)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:321)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:313)

Here are the steps to recreate the issue. First, I set up the database like so:

docker run -p 7654:5432 --name zoo_postgres -e POSTGRES_PASSWORD="stranger" -e POSTGRES_USER=stranger -d postgres

And run a setup script, that looks like this:

create DATABASE stranger;
create SCHEMA   stranger;

CREATE TABLE prescriptions (
  prescription_id BIGINT NOT NULL,
  PRIMARY KEY (prescription_id)
);

CREATE USER stranger_user WITH ENCRYPTED password 'stranger_user';

grant CONNECT on DATABASE stranger to stranger_user;
grant USAGE on SCHEMA stranger to stranger_user;

GRANT SELECT ON ALL TABLES IN SCHEMA stranger TO stranger_user;
GRANT INSERT ON ALL TABLES IN SCHEMA stranger TO stranger_user;
GRANT DELETE ON ALL TABLES IN SCHEMA stranger TO stranger_user;
GRANT UPDATE ON ALL TABLES IN SCHEMA stranger TO stranger_user;

-- I've tried several variations of the line below, without any effect
ALTER USER stranger_user SET SEARCH_PATH to stranger,stranger_user,public;

On the Java side, the code is extremely simple and looks like the following:

public static void main(String[] args) throws Exception {
    Class.forName("org.postgresql.Driver");
    Connection connection = DriverManager.getConnection("jdbc:postgresql://localhost:7654/stranger","stranger_user", "stranger_user"); // this succeeds (suggesting the connection string is correct)
    connection.setCatalog("stranger");
    connection.setSchema("stranger");
    Statement stmt = connection.createStatement();
    stmt.execute("select prescription_id from prescriptions");
    // code dies before getting to the close line.
    connection.close();
    System.out.println("Successfully connected using JDBC directly");
}

Setting the catalog and schema has no effect and neither does changing the search path. Everything is lower case, so I don't think it has anything to do with escaping the table names. I've tried using the real user (and skipping the user account), omitting the database name and several other tricks. I suspect that there's something wrong with the way that I'm connecting to the database, but I can connect to is using psql, like so:

% psql -h localhost -p 7654 -d postgres -U stranger_user

From this point, I can access the tables with normal sql:

% psql -h localhost -p 7654 -d postgres -U stranger_user     
Password for user stranger_user: 
psql (9.4.6, server 9.5.2)
WARNING: psql major version 9.4, server major version 9.5.
         Some psql features might not work.
Type "help" for help.

postgres=> \d
              List of relations
  Schema  |     Name      | Type  |  Owner   
----------+---------------+-------+----------
 stranger | prescriptions | table | stranger
(1 row)

postgres=> select * from prescriptions;
 prescription_id 
-----------------
(0 rows)

postgres=> select * from stranger.prescriptions;
 prescription_id 
-----------------
(0 rows)

I'm using the latest version of the postgres driver: "org.postgresql:postgresql:9.4+", and can't see anything else that would cause this issue. At this point I'm out of ideas and can't figure out what's wrong.

Answer 1

You're connecting with psql to port 7654, and then in your JDBC URL to port 6543. Also, you're connecting to the postgres database with psql, and the stranger database in your JDBC URL.

Could these differences be the source of the exception you're seeing?

https://jdbc.postgresql.org/documentation/94/connect.html

Answer 2

If you use pgAdmin or psql, and log in to server localhost:6543, database stranger with user stranger_user and password stranger_user, what do you see?

I expect that you see an empty public schema.

Running create DATABASE stranger does not make it the active database.

So, running create SCHEMA stranger will create that schema in the postgres database. It also won't make the new schema active.

So, running CREATE TABLE prescriptions will create that table in the public schema of the postgres database.

Of course, all that is not even in the PostgreSQL database instance/cluster served on port 6543, because you did all that on a different instance/cluster on port 7654.