# PostgreSQL Schema Search Path

> In PostgreSQL, the SET SEARCH_PATH command is used to set the schema search path. The “SHOW” command is used to demonstrate the current schema search path.

In RDBMS like Postgres, the schemas are used to define a way of organizing the data. It is a collection of logical structures such as tables, views, data types, constraints, functions, etc. In PostgreSQL, the schema **SEARCH_PATH** represents an environment variable. By default, the objects are created in the public schema, however, setting the **SEARCH_PATH** allows us to store the tables, views, functions, etc. in a particular schema.

This post presents a detailed guide on the Postgres search path.

 **PostgreSQL Schema Search Path**

The following topics will be covered regarding the PostgreSQL schema search path using practical demonstration:

\- How to Show the Current Search Path in Postgres?  
\- How to Set the Search Path for the Current Session?  
\- How to Set a Permanent Search Path for a Database?  
\- How to Reset SEARCH_PATH for a Specific Database?  
\- How to Set a Permanent Search Path for a User/Role?  
\- How to Reset SEARCH_PATH for a Specific Role/User?

 **How to Show the Current Search Path in Postgres?**

In Postgres, the “SHOW” command is used to demonstrate the current schema search path:
    
    
    SHOW SEARCH_PATH;

The output displays that the current search path is public.

 **How to Set Search Path for Current Session?**

Execute the “\dn” command to fetch the available schemas:
    
    
    \dn;

Suppose we want to set the search path from “public” to “postgres_schema” for the current session. For this purpose, we will use the “SET” command as follows:
    
    
    SET SEARCH_PATH = postgres_schema;

The search path has been set for the current session. Execute the “SHOW” command for the confirmation:
    
    
    SHOW SEARCH_PATH;

The output snippet demonstrates that the “postgres_schema” has been set as the default search path for the current session. The search path will be reset to the "public" schema once the current session expires.

 **How to Set a Permanent Search Path for a Database?**

To set a permanent search path for a database, use the “ALTER DATABASE” command followed by the “SET SEARCH_PATH” command:
    
    
    ALTER DATABASE postgres SET SEARCH_PATH TO postgres_schema;

\- Here, “postgres” is the name of the current database.  
\- The “postgres_schema” represents the schema to be set as the search path.  
\- The above command will change the schema search path at the database level, permanently.

For confirmation, execute the “SHOW SEARCH_PATH” command:
    
    
    SHOW SEARCH_PATH;

The output shows that the schema search path has been set successfully.

 **How to Reset SEARCH_PATH for a Specific Database?**

Execute the ALTER DATABASE command along with the RESET command to unset the current SEARCH_PATH to the default SEARCH_PATH:
    
    
    ALTER DATABASE postgres RESET SEARCH_PATH;

Let’s confirm the search path for the “postgres” database using the following command:
    
    
    SHOW SEARCH_PATH;

The search path has been unset to the default schema/search path.

 **How to Set a Permanent Search Path for a User/Role?**

Use the “ALTER USER” command with the “SET” command to set the search path at the user level:
    
    
    ALTER USER postgres SET SEARCH_PATH TO example_user;

To confirm the search path for the “postgres” user, execute the “SHOW” command as follows:
    
    
    SHOW SEARCH_PATH;

The search path for the “postgres” user has been successfully changed to the “example_user”.

 **How to Reset SEARCH_PATH for a Specific Role/User?**

Use the ALTER USER or ALTER ROLE command with the RESET command to rest the search path for a specific role:
    
    
    ALTER USER postgres RESET SEARCH_PATH;

To confirm the search path for the “postgres” user use the “SHOW” command, as follows:
    
    
    SHOW SEARCH_PATH;

The search path for the “postgres” user has been reset to the default search path.

 **Conclusion**

In PostgreSQL, the SET SEARCH_PATH command is used to set the schema search path. The SET SEARCH_PATH sets the search path for the current session only, however, it can be set permanently at the user or database level. For this purpose, use the SET SEARCH_PATH command along with the ALTER DATABASE or ALTER ROLE command. This post presented a detailed guide on how to set or reset the default schema search path in Postgres.

---
[View this page online](https://www.commandprompt.com/education/postgresql-schema-search-path/)