223

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

lospejos
  • 1,976
  • 3
  • 19
  • 35
marcosbeirigo
  • 11,098
  • 6
  • 39
  • 57

9 Answers9

310

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
rogerdpack
  • 62,887
  • 36
  • 269
  • 388
Hiro2k
  • 5,254
  • 4
  • 23
  • 28
  • 2
    Yes but at the moment of the writing (late 2012) it's not a part of the [9.1 driver](http://jdbc.postgresql.org/documentation/91/), see: [Connection Parameters](http://jdbc.postgresql.org/documentation/91/connect.html#connection-parameters). – user272735 Sep 14 '12 at 06:51
  • Did you try it? Because it wasn't listed as part of the previous driver but it still worked. – Hiro2k Sep 27 '12 at 13:18
  • 8
    Tried with 9.3-1101-jdbc41 and 9.1, doesn't work for me – Ignacio A. Poletti Jun 03 '14 at 14:30
  • @IgnacioA.Poletti Try using the JDCB `setSchema` method after creating your connection. Works for me with a recent postgres driver. – beldaz Jun 19 '15 at 19:47
  • searchpath didn't work.... using 9.4. see: https://jdbc.postgresql.org/documentation/94/connect.html#connection-parameters – pablo.vix Aug 30 '15 at 18:39
  • It looks like they added a new currentSchema according to that link. – Hiro2k Aug 31 '15 at 19:14
  • 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 – Erhannis Jul 06 '16 at 15:13
  • 7
    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. – SebastianH Sep 30 '16 at 15:18
  • It worked for me for JDBC version 9.4-1206, Thanks for the info – DuSant Mar 08 '17 at 15:53
87

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

For example:

jdbc:postgresql://localhost:5432/mydatabase?currentSchema=myschema
Steve
  • 8,066
  • 11
  • 70
  • 112
53

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'
chzbrgla
  • 5,158
  • 7
  • 39
  • 56
  • 2
    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; – Flowchartsman Sep 05 '14 at 19:21
46

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.

Herks
  • 497
  • 4
  • 6
  • 2
    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(); }` – romeara Sep 30 '14 at 18:42
  • There is a way to specify the default schema in the connection string (jdbc uri). See answers below. – acorello Jun 02 '15 at 08:29
9

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.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
  • 4
    "*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`](https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH) –  Jul 09 '19 at 06:13
8

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

postgres://user:password@host/dbname?sslmode=disable&search_path=schema
Rafael Barros
  • 1,043
  • 18
  • 25
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/

Scott Langley
  • 374
  • 4
  • 3
4

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.

beldaz
  • 4,299
  • 3
  • 43
  • 63
2

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

Oleksandr Fedirko
  • 21
  • 1