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.
