PSQLException: ERROR: relation "TABLE_NAME" does not exist
hibernate, java, postgresql
Solution
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`
Problem
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.