CODEWITHSUNDEEP
postgresql
user3221325
user3221325

Reputation: 631

Postgres table visible in psql, not from jdbc

I've created a table called inventory in a Postgres 9.1 database. I have been able to select, insert, etc. from psql.

However, I'm trying to access this table from the server of a webapp. I have installed the server code (using JDBC) on the same machine as the Postgres database. When I try to access the inventory table from the JDBC code, I get this error:

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

I have made sure that I'm using the same username, that I'm being consistent between the properties used to create the connection, the user I'm checking in psql, and by getting the username from the JDBC metadata at runtime.

Oddly, I've had success with a similar setup on my laptop (hosting Tomcat and the webapp and the postgres installation all there), but the setup doesn't work when I host the db and the webapp on a server.

Since it works on one machine and not another, I think the following are possible explanations:

  1. I have something wrong in the user configurations. Perhaps the Postgres user permissions are wrong for access from JDBC on the server (I didn't set up the Postgres installation on the server).
  2. I'm hitting the wrong schema. How would I know?
  3. I'm hitting the wrong machine somehow. I tried select inet_server_addr();, but it only says 127.0.0.1, which isn't very useful. Is there a better way to check?

In another conversation ("relation not found" using Postgres within an Eclipse Hibernate application), I saw that many people trying to help wanted to know if the table appeared in psql. For that problem, the answer was no, and he just needed to create the table. For me, the answer is yes, and I just can't see the table.

Any idea why I can connect to a database but not see a table that my user owns?

Thanks

Upvotes: 0

Views: 3846

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324851

This has to be one of a few possibilities:

  • You're connecting to the wrong server.
  • You're connecting to the wrong database on the right server
  • Your search_path differs between the two connections
  • Your identifier quoting differs between the two connections
  • You've made a typo

For the first two, connect with PgJDBC, set application_name, and then query pg_stat_activity from psql to make sure the connection is to the server you expect, to the DB you expect.

For search_path, SHOW search_path; on both connections. Compare. Also look at \dt inventory in psql, note the schema the table is in, and try accessing it by its qualified name in PgJDBC e.g. someschema.inventory.

For quoting: just that, is the table maybe named "Inventory" or "INVENTORY"? If so, could you be referring to it as INVENTORY in one place and "INVENTORY" in another? Pg case-folds unless quoted, per the SQL standard.

Upvotes: 2

Related Questions