PSQLException: ERROR: relation "TABLE_NAME" does not exist

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.

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

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.

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();