3

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.

Sinan Gedik
  • 92
  • 2
  • 7
  • More ways to set the `search_path`: http://stackoverflow.com/questions/9067335/how-to-create-table-inside-specific-schema-by-default-in-postgres/9067777#9067777 – Erwin Brandstetter Apr 20 '13 at 12:53

1 Answers1

3

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....

  • This way, I need to go to database twice. One for setting the schema and the second for running a query. I just want to determine the schema and go to database once. – Sinan Gedik Apr 21 '13 at 06:23
  • @SinanGedik You only set this **once** in the session and the change will be in effect as long as the connection is open (or you change the schema again). You do not need to do this for every query. If you alter the user, you don't need to do this ever again. So maybe changing the user is what you are after. –  Apr 21 '13 at 06:57