Relation doesn't exist error with postgres 10.1 and hibernate

Question

I have recently moved to postgres 10.1 database from oracle. I created all the tables manually via sql queries and made sure that I do not use any double quotes around table/column names.

CREATE TABLE MYSCHEMA.SOMETABLE (ID VARCHAR(20) NOT NULL primary key, DISPLAYTEXT VARCHAR(100)));// skipping other column names for brevity

Entity Class in code

@Entity
@Table(name = "MYSCHEMA", schema = "SOMETABLE")
public class SomeTable implements Serializable {

    private static final long serialVersionUID = -4856666041227614340L;
    @Column(name = "VERSION")
    private String version;

    @Column(name = "DISPLAYTEXT")
    private String displayText;

    //other columns
}

Using Jpa repository, I am querying this table.

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef  = "someEntityManagerFactoryContentDB",
        transactionManagerRef = "sometransactionManagerContentDB",
        basePackages = {"com.somepath.repository.content"})
public interface SomeSearchConfigRepository extends JpaRepository<SomeTable, String> {
    List<SomeTable> findBySearchTypeAndActiveOrderBySequenceAsc(
            String searchType, String active);
}

The issue is that it's not able to find this table and throws below error.

2019-06-24 09:56:26,781  WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper:SQL Error: 0, SQLState: 42P01
2019-06-24 09:56:26,782 ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper:ERROR: relation "MYSCHEMA.SOMETABLE" does not exist

Sql generated as per the logs

Hibernate:
    /* select
        generatedAlias0
    from
        SomeTable as generatedAlias0
    where
        (
            generatedAlias0.searchType=:param0
        )
        and (
            generatedAlias0.active=:param1
        )
    order by
        generatedAlias0.sequence asc */ select
            myalias_."ID" as ID1_3_,
            myalias_."ACTIVE" as ACTIVE2_3_,
            myalias_."COLUMNNAME" as COLUMNNA3_3_,
            myalias_."DATATYPE" as DATATYPE4_3_,
            myalias_."DISPLAYTEXT" as DISPLAYT5_3_,
            myalias_."LENGTH" as LENGTH6_3_,
            myalias_."LINKCOLUMN" as LINKCOLU7_3_,
            myalias_."LINKSEARCH" as LINKSEAR8_3_,
            myalias_."LOOKUPCOLUMN" as LOOKUPCO9_3_,
            myalias_."LOOKUP_CATEGORY" as LOOKUP_10_3_,
            myalias_."MULTISELECT" as MULTISE11_3_,
            myalias_."REFERENCEKEY" as REFEREN12_3_,
            myalias_."REFERENCETABLENAME" as REFEREN13_3_,
            myalias_."REFERENCEVALUE" as REFEREN14_3_,
            myalias_."SEARCHTYPE" as SEARCHT15_3_,
            myalias_."SEQUENCE" as SEQUENC16_3_,
            myalias_."TABLENAME" as TABLENA17_3_,
            myalias_."VERSION" as VERSION18_3_
        from
            "MYSCHEMA"."SOMETABLE" myalias_
        where
            myalias_."SEARCHTYPE"=?
            and myalias_."ACTIVE"=?
        order by
            myalias_."SEQUENCE" asc

Is it that somehow the annotations in entity classes causing this issue? What's going wrong here and how can I fix this issue with minimal changes in my code ?

You need to tell your obfuscation layer to stop quoting the names. Did you try @Table(name = "myschema", schema = "sometable")? — user330315
– user330315, Commented Jun 24, 2019 at 11:21

Simon Martinelli · Accepted Answer · 2019-06-24 13:42:02Z

1

I assume that you are using Spring Boot.

One option is to tell Hibernate to don't quote the names

spring.jpa.properties.hibernate.globally_quoted_identifiers=false

Or the other option is to create your tables also with quotes and go for all UPPERCASE names

edited Jun 24, 2019 at 13:42

answered Jun 24, 2019 at 13:12

Simon Martinelli

36.6k6 gold badges62 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Naxi Over a year ago

I am using <prop key="hibernate.globally_quoted_identifiers">true</prop> in my xml file. The reason for this is that I have few of the tables which are using reserved words as column names. I have the need of running the same code on oracle as well as on myql/postgres. So to make sure I do not use db specific escape characters, i used the above property.

Simon Martinelli Over a year ago

OK But then with Postgresql the tables are case sensitive. I assume that your tables and columns are now lower case and your mapping has upper case names.

Collectives™ on Stack Overflow

Relation doesn't exist error with postgres 10.1 and hibernate

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related