How to set default schema in Postgres?

In this article, I would like to go through what are the different ways to set default schema in Postgres.

I came up with the thought of writing this article based on my recent experience in my work project which were thought of moving all our DDL and DML objects from the default (public) to new schema. Basically, its a matured projects and lot of schema references. Some of the database (DDL, DML) objects are referenced in the code base/DB scripts with fully qualified name and some of them don’t.

So, the challenge is how to change all the existing and new references to the new schema?

Ideally, we have thought to

• Remove all the explicit schema references in the codebase.
• Set the default schema to new schema at the user or database level.

Check the current/default schema

SHOW SEARCH_PATH;

Change the default Schema at Session Level

SET SEARCH_PATH=newschema;

Change the default schema at Database Level

ALTER DATABASE <db_name> SET search_path TO <schema_name>;

Change the default schema at User Level

ALTER ROLE|USER <role_name> SET search_path to <schema_name>;

This will ensure no more explicit schema reference as a future proof and for the new database (DDL, DML) objects we don’t need to explicitly specify the schema name. Because the schema set at the user level or database level will qualify all the database objects without schema name at the beginning.

But due to the Postgres running in the restricted environment, we don’t have enough privilege to set default schema at the user or database level.

So, How can we overcome this restriction? what would be alternative to set the default schema?

Finally, we have found a way to set default schema via connection string.

Basically we can set the default schema using SearchPath in the connection strings.

We can define connection string in different formats. There are few as below.

Host=<host_name>;Port=<port_no>;Database=<database_name>;User ID=<user_name>;Password=<passwoed>;Pooling=true;SearchPath=<schema_name>;

If we can’t specify the SearchPath as above, we can use the additional connection parameter ?option=-c search_path=<schema_name>

postgresql://username:password@host:port/database?option=-c search_path=<schema_name>

Hope it gives an idea on how to set default schema in the connection string if we can’t set via Alter Database or Alter User command.

Happy SQL’ing 🙂