What is search path in Postgresql and how to configure it?

In PostgreSQL, the `search_path` is a configuration parameter that determines which schemas the system will check and in what order when an object (like a table or a function) is referenced by a simple, unqualified name.

By default, the `search_path` is set to `$user, public`, which means the system will first look for objects in a schema with the same name as the current user, and if it doesn’t find the object there, it will look in the `public` schema.

You can view the current `search_path` setting with the following command:

SHOW search_path;

You can change the `search_path` for the current session with the SET command:

SET search_path TO myschema, public;

In this example, PostgreSQL will first look in `myschema` and then in `public` when an unqualified object name is referenced.

If you don’t set the search path in PostgreSQL and try to access a table or other database object without qualifying its schema, you may encounter the following error:

ERROR: relation "tablename" does not exist

By default, the search path includes the `public` schema, so if your object is in a different schema and you haven’t included that schema in the search path, PostgreSQL won’t be able to locate the object.

So this error occurs when PostgreSQL cannot find the specified table or object in the schemas listed in the current search path.

To resolve this error, you can either:

•  Set the search path to include the schema where your object is located, using a command like `SET search_path TO my_schema, public;`.
• Qualify the object name with its schema, for example `SELECT * FROM my_schema.mytable;`.

 

It’s important to ensure that the search path is correctly set to include all the schemas you’ll be accessing in your session, or to consistently use fully qualified names for all database objects.

How to config search path in Postgresql?

The search path in PostgreSQL can be set at multiple levels – user level, database level, or session level.

1. Session level: You can also set the search path for the current session only. This will override any defaults set at the user or database level.

SET search_path TO schema_name;

2. User level: You can set the search path for a specific user. This will be the default search path whenever that user connects to the database.

ALTER USER username SET search_path TO schema_name;

3. Database level: You can set the search path for a specific database. This will be the default search path for any session connecting to that database unless overridden at the user level or session level.

ALTER DATABASE database_name SET search_path TO schema_name;

To show the search path for a specific database, you can query the pg_settings view, which provides access to database configuration parameters.

Here’s how you can do it:

SELECT setting FROM pg_settings WHERE name = 'search_path';

When you run this command, it will return the current search_path setting for your active database connection.

Please note that the search_path may be overridden at the user level or the session level. So, the result of this command might not necessarily reflect the database-level setting if a user-level or session-level setting is in place.