Is it possible to specify the schema when connecting to postgres with JDBC?

Question

Is it possible? Can i specify it on the connection URL? How to do that?

Solution 1

I know this was answered already, but I just ran into the same issue trying to specify the schema to use for the liquibase command line.

Update As of JDBC v9.4 you can specify the url with the new currentSchema parameter like so:

jdbc:postgresql://localhost:5432/mydatabase?currentSchema=myschema

Appears based on an earlier patch:

http://web.archive.org/web/20141025044151/http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-td2174512.html

Which proposed url's like so:

jdbc:postgresql://localhost:5432/mydatabase?searchpath=myschema

Solution 2

As of version 9.4, you can use the currentSchema parameter in your connection string.

For example:

jdbc:postgresql://localhost:5432/mydatabase?currentSchema=myschema

Solution 3

If it is possible in your environment, you could also set the user's default schema to your desired schema:

ALTER USER user_name SET search_path to 'schema'

Solution 4

I don't believe there is a way to specify the schema in the connection string. It appears you have to execute

set search_path to 'schema'

after the connection is made to specify the schema.

Solution 5

DataSource – setCurrentSchema

When instantiating a DataSource implementation, look for a method to set the current/default schema.

For example, on the PGSimpleDataSource class call setCurrentSchema.

org.postgresql.ds.PGSimpleDataSource dataSource = new org.postgresql.ds.PGSimpleDataSource ( );
dataSource.setServerName ( "localhost" );
dataSource.setDatabaseName ( "your_db_here_" );
dataSource.setPortNumber ( 5432 );
dataSource.setUser ( "postgres" );
dataSource.setPassword ( "your_password_here" );
dataSource.setCurrentSchema ( "your_schema_name_here_" );  // <----------

If you leave the schema unspecified, Postgres defaults to a schema named public within the database. See the manual, section 5.9.2 The Public Schema. To quote hat manual:

In the previous sections we created tables without specifying any schema names. By default such tables (and other objects) are automatically put into a schema named “public”. Every new database contains such a schema.

View more solutions

Comments

[user272735]

Yes but at the moment of the writing (late 2012) it's not a part of the 9.1 driver, see: Connection Parameters.

[Hiro2k]

Did you try it? Because it wasn't listed as part of the previous driver but it still worked.

[Ignacio A. Poletti]

Tried with 9.3-1101-jdbc41 and 9.1, doesn't work for me

[Flowchartsman]

Probably better to ALTER the database itself so that the same user can connect to different databases with different search_paths if need be: ALTER DATABASE dbname SET search_path TO public,schemaname;

[romeara]

This worked for me, specifically using the "Connection" instance to run: Statement statement = connection.createStatement(); try { statement.execute("set search_path to '" + schema + "'"); } finally { statement.close(); }

[acorello]

There is a way to specify the default schema in the connection string (jdbc uri). See answers below.

[beldaz]

@IgnacioA.Poletti Try using the JDCB setSchema method after creating your connection. Works for me with a recent postgres driver.

[pablo.vix]

searchpath didn't work.... using 9.4. see: jdbc.postgresql.org/documentation/94/…

[Hiro2k]

It looks like they added a new currentSchema according to that link.

[Erhannis]

Neither of these work for me. When I prepend "schema." to my table names, it DOES work, so it should indeed be a problem of just not being in the right schema, but neither of the above options themselves appear to set the schema. PostgreSQL 9.5

[SebastianH]

We solved this problem by also using a different (newer) JDBC driver. In our case postgresql-9.4.1209.jdbc42.jar worked together with a 9.5 database and the ?currentSchema=myschema syntax.

[DuSant]

It worked for me for JDBC version 9.4-1206, Thanks for the info

[a_horse_with_no_name]

"attempts to connect to a schema" - That's a bit misleading. The driver does not connect "to a schema", but to a database. Which schema is used by queries depends on the current setting of the search_path