PostgreSQL: The Schema Search Path and change the default PUBLIC Schema

Most of the PostgreSQL Professionals are wondering about that why objects are stored in default public schema when they have not supplied schema identifier.

The Schema Search Path of the PostgreSQL:

The best practice is to provide a schema identifier for each and every database object, but also this is one of the important topic about schema identifier because sometimes specifying an object with the schema identifier is a tedious task.

The currently default schema search path is PUBLIC schema so whenever we are creating any object with schema identifier, it creates in the default PUBLIC schema.

Below is a script to check current search path:

For example, I create one table without a schema identifier, and see it stores in the default PUBLIC schema.

Below are a two SELECT statements which have no any difference because when we are using the database object without the schema identifier, it fetches from the default PUBLIC schema.

If we are dealing with multiple schema in a single database, I suggest you to always provide Schema Identifier for each and every database object.

But when we are dealing with only one schema and if you want to change your default schema search path, you can use below script to change default schema search path.

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of