Change schema dynamically

Question

How can I set Postgres schema dynamically in Java? I tried doing:

this.getDataSource().getConnection().setSchema("mySchema");

I am using spring-jdbc and this is a JdbcDaoSupport instance.

Note: I don't want to go to database twice, so set search_path does not solve my problem efficiently.

Answer 1

Run the statement:

set schema 'myschema';

to change the current schema

Or simply set the search path, so that you can access the tables in e.g. the public and myschema:

set search_path to public, myschema;

(Note the difference in how you specify the schema name in the two statements: the first one has to use single quotes, the second one does not)

You can also change the search path permanently for a specific user, by using alter user....