How to Specify the Schema When Connecting to Postgres with Jdbc

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

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

Connect with postgreSQL schema

You should add search_path=myschema to dataSourceName

P.S. better use fmt.Sprintf("host=%s port=%d dbname=%s user=%s password='%s' sslmode=disable search_path=%s", ...) instead ``+``

How to specify a schema name in the Postgres URL for connecting to a PostgreSQL database on Heroku

I don't think you can,

what you can do however is associate a schema search path with a database user, so if you want a different schema you'd need to use a different username to connect as.

SQL:

alter user fred set search_path to 'gc';

Setting schema in PostgreSQL JDBC doesn't seem to work

user is a built-in function (and a keyword). So you can't really use it as a table name:

psql (10.4)
Type "help" for help.

postgres=# select user;
   user
----------
 postgres
(1 row)

postgres=# select * from user;
   user
----------
 postgres
(1 row)

And because it's a function it does not have a column name.

postgres=# select name from user;
ERROR:  column "name" does not exist
LINE 1: select name from user;
               ^
postgres=#

If you qualify the table, then it's clear that you are not referencing the function, but the table.

You can either always qualify the table name with the schema, or use double quotes: select name from "user"; or simply find a table name that does not collide with built-in functions.

kafka-connect JDBC PostgreSQL Sink Connector explicitly define the PostgrSQL schema (namespace)

Use the options connection parameter to set the search_path:

jdbc:postgresql://<host>:<port5432>/<database>?options=-c%20search_path=myschema,public

How to define the schema property in tomcat connection

Unlike HikariCP and DBCP2, Tomcat JDBC does not have a "default schema" property (cf. available properties).

Fortunately, since you are using PostgreSQL, you can set the default schema as connection property by:

• either adding it to the URL: jdbc:postgresql://host:port/database?currentSchema=my-schema
• or by adding currentSchema=my-schema in the connectionProperties property of the datasource, which in Spring YAML should look like:

spring:
  datasource:
    tomcat:
      connection-properties: "currentSchema=my-schema"

See also: PostgreSQL JDBC Connection Parameters

How to select a specific Postgres schema with reactive datasource in Quarkus

Adding search_path to the connection uri will fetch results from specific schema.

Below configuration worked.

postgresql://localhost:5432/sampledb?search_path=user1

Below is the class that parse the db configuration

io.vertx.pgclient.impl.PgConnectionUriParser

Thank You David for the leads.

Reference : https://vertx.io/docs/vertx-pg-client/java/#_connection_uri

Postgre SQL Schema Bind of Connection

I solved this issue by simply adding a schema.table ..

as an example i give the schema as input parameter and store it to String schema.

if i call a query like:

Select * from TABLE;

it looks now like

Select * from schema.TABLE;

and it works ;)

---

Related Topics