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

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

Answer 2

If you are using spring-boot, set default schema in the configuration:

spring.jpa.properties.hibernate.default_schema: my_schema

Make sure to include the schema name in the query:

@Query(value = "SELECT user_name FROM my_schema.users", nativeQuery = true)
List<String> findAllNames();

Answer 3

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 4

Due to using column definations I was getting this error

issue @Column(name = "user_id", nullable = false, unique = true, columnDefinition ="user unique ID" )

solved @Column(name = "user_id", nullable = false, unique = true)

Thanks

Answer 5

Not sure about others but my case if worked only if I wrote my query like this:

@Query(value = "SELECT tbl.* FROM my_schema.my_table tbl", nativeQuery = true)

Always have some reference "tbl" for table and use it in the query.

Answer 6

In my case I had the schema name in uppercase letters, I changed the schema name to lowercase letters.

In the entities I changed the mapping as follows

from @Table(name="table_name", schema = "SCHEMA") to @Table(name="table_name", schema = "schema")