PSQLException: ERROR: relation "TABLE_NAME" does not exist

Question

I am trying to run hibernate on a PostgreSQL 8.4.2 DB. Whenever I try to run a simple java code like:

List<User> users = service.findAllUsers();

I get the following error:

PSQLException: ERROR: relation "TABLE_NAME" does not exist

Since I have option hibernate.show_sql option set to true, I can see that hibernate is trying to run the following SQL command:

    select this_.USERNAME as USERNAME0_0_, this_.PASSWORD as PASSWORD0_0_ 
from "TABLE_NAME" this_

When in reality, it should at least run something like:

    select this_."USERNAME" as USERNAME0_0_, this_."PASSWORD" as PASSWORD0_0_ 
from "SCHEMA_NAME"."TABLE_NAME" as this_

Does anyone know what changes I need to make for Hibernate to produce the right SQL for PostgreSQL?

I have set up the necessary postgreSQL datasource in applicationContext.xml file:

<!-- Use Spring annotations -->
 <context:annotation-config /> 
 <!-- postgreSQL datasource -->
 <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
  destroy-method="close">
  <property name="driverClassName" value="org.postgresql.Driver" />
  <property name="url"
   value="jdbc:postgresql://localhost/DB_NAME:5432/SCHEMA_NAME" />
  <property name="username" value="postgres" />
  <property name="password" value="password" />
  <property name="defaultAutoCommit" value="false" />
 </bean>

On the same file I have set up the session factory with PostgreSQL dialect:

<!-- Hibernate session factory -->
 <bean id="sessionFactory"   class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
  <property name="dataSource" ref="dataSource" />
  <property name="annotatedClasses">
   <list>
    <value>com.myPackage.dbEntities.domain.User</value>
   </list>
  </property>
  <property name="hibernateProperties">
   <props>
    <prop key="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</prop>
    <prop key="hibernate.show_sql">true</prop>
   </props>
  </property>
 </bean>
 <!-- setup transaction manager -->
 <bean id="transactionManager"
  class="org.springframework.orm.hibernate3.HibernateTransactionManager">
  <property name="sessionFactory">
   <ref bean="sessionFactory" />
  </property>
 </bean>

Finally, the way I am mapping the domain class to the table is:

    @Entity
@Table(name = "`TABLE_NAME`")
public class User {
@Id
@Column(name = "USERNAME")
private String username;

Has anyone encountered a similar error?. Any help in solving this issue will be much appreciated. Please note that question is different to post Cannot simply use PostgreSQL table name (”relation does not exist”)

Apologies for the lengthy post.

Answer 1

Looking at the PostgreSQL JDBC driver documentation, it doesn't seem to support you adding the schema at the end of the connection url. Are you sure that's supposed to work?

A workaround would be to set the search_path in the database to include your schema, but that would obviously fail if you have the same table in multiple schemas.

I don't know hibernate enough to comment on if it's possible to teach it about a schema.

Answer 2

You need to specify the schema name in the Spring's Hibernate properties, not in the JDBC connection URL:

<prop key="hibernate.default_schema">SCHEMA_NAME</prop>

That said, your JDBC connection URL is in fact syntactically invalid. According to the PostgreSQL JDBC documentation you have to use one of the following syntaxes:

• jdbc:postgresql:database
• jdbc:postgresql://host/database
• jdbc:postgresql://host:port/database

The database is here the database name. If the host is left away, it will default to localhost. If the port number is left away, it will just default to 5432. Thus, one of the following is valid in your case:

• jdbc:postgresql:DB_NAME
• jdbc:postgresql://localhost/DB_NAME
• jdbc:postgresql://localhost:5432/DB_NAME