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

Question

I am connecting my parse-server application to a PostgreSQL database hosted on the Heroku-PostgreSQL service.

My database is with a schema called gc which is different to the default public schema on Postgresql.

I used the following to connect to the database from my parse-server application.

"postgres://{USERNAME}:{PASSWORD}@{HOSTNAME_ON_AWS}:5432/{DATABASE_NAME}?ssl=true" 

But the issue was it was connected to the public schema but not the gc schema I wanted.

Is there a way to specify the schema name in Postgres URL?

Answer 1

I attach the parameter options=-csearch_path=XXX to the URI. It works.

psql "postgresql://user:[email protected]:6789/postgres?options=-csearch_path%3Dabc" 

It shows the current schema below.

postgres=> show search_path;
 search_path
-------------
 abc
(1 row)

And take notice that I use the URLencode for the character =. Its URL encoding is %3D. Otherwise, you can't get the correct result in the shell.

Here are some references:

https://www.postgresql.org/docs/current/libpq-connect.html https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS

Answer 2

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 can to use a different username to connect as.

SQL:

alter user fred set search_path to 'gc';