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?

Capernaum

Answer 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

Coenobite

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

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

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

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

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

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

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 – Voiceful

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

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

Answer 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

Fenestella

Answer 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'

Janeenjanek

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; – Strung

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

Vesicatory

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(); } – Mucker

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

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

Ascetic

"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 – Desrosiers

Answer 6

In Go with "sql.DB" (note the search_path with underscore):

postgres://user:password@host/dbname?sslmode=disable&search_path=schema

Opsonize

Answer 7

I submitted an updated version of a patch to the PostgreSQL JDBC driver to enable this a few years back. You'll have to build the PostreSQL JDBC driver from source (after adding in the patch) to use it:

http://archives.postgresql.org/pgsql-jdbc/2008-07/msg00012.php

http://jdbc.postgresql.org/

Viperish

Answer 8

I have tried: currentSchema, searchpath, search_path in the key-value section and nothing works for me. I have found work around with the "options" key word:

postgresql://host:port/dbname?options=-c%20search_path%3Dschema_name

Bultman

asyncpg doesn't support it. – Meteorite

Answer 9

Don't forget SET SCHEMA 'myschema' which you could use in a separate Statement

SET SCHEMA 'value' is an alias for SET search_path TO value. Only one schema can be specified using this syntax.

And since 9.4 and possibly earlier versions on the JDBC driver, there is support for the setSchema(String schemaName) method.

Oracular

Answer 10

Updated on 2024

If you want to set schema, use ?options=-c%20search_path=

Full url

jdbc:postgresql://localhost:5432/postgres?options=-c%20search_path=test,public,pg_catalog

According to the postgresql JDBC doc https://jdbc.postgresql.org/documentation/use/

Wye