167

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

lospejos
  • 1,888
  • 3
  • 17
  • 31
marcosbeirigo
  • 10,238
  • 6
  • 37
  • 56

9 Answers9

233

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
  • 50,731
  • 31
  • 212
  • 332
Hiro2k
  • 4,377
  • 4
  • 21
  • 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
  • Thank you, it works for me. – Kamal SABBAR Apr 02 '21 at 16:58
75

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

For example:

jdbc:postgresql://localhost:5432/mydatabase?currentSchema=myschema
Distortum
  • 6,740
  • 9
  • 58
  • 103
50

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,007
  • 6
  • 36
  • 55
  • 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; – Alaska 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. – protoboolean Jun 02 '15 at 08:29
8

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
  • 218,480
  • 72
  • 657
  • 915
  • 3
    "*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) – a_horse_with_no_name Jul 09 '19 at 06:13
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
  • 334
  • 3
  • 3
3

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
  • 3,849
  • 3
  • 38
  • 58
3

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

postgres://user:password@host/dbname?sslmode=disable&search_path=schema
a_horse_with_no_name
  • 440,273
  • 77
  • 685
  • 758
Rafael Barros
  • 904
  • 14
  • 24
0

Note : This is unrelated to jdbc url

I am connecting to the server via psql installed in my vm(which is a sql client), this code worked for me like a charm

options=--search_path={your_schema_name}

Example:

psql "host={your_host} port={your_port} dbname={your_dbname} user={your_user} password={your_password} options=--search_path={your_schema_name}"
Vincent Singha
  • 25
  • 7