Search code examples
sqldatabasepostgresqldatabase-migration

PosgreSQL - ERROR: relation "table_name" does not exist. How to query without schema name?

I am migrating from oracle to postgreSQL. In my application I have a lot of queries like this 

ResultSet resultSet = statement.executeQuery("SELECT NAME FROM Table_name");

But I am failing with exception

ERROR: relation "table_name" does not exist.

As far as I understood I need also provide schema name, and when I am performing like this, it works:

ResultSet resultSet = statement.executeQuery("SELECT NAME FROM schema_name.Table_name");

So basically my question is - How could I avoid refactor all queries?

Solution

  • This command, as mentioned above, will set search path for current session

    SET search_path TO schema_name,public;

    However if there is need to do it for all session, just correct config file - pgsql/11/data/postgresql.conf add search path 

    search_path = '"$user", YOUR_SCHEMA'

    Restart the DB.