View and modify current_schema and search_path in Redshift database

Run the below select statement to view the ‘current schema’. ‘Current schema’ is the default schema for database object creation in Redshift database.

select current_schema();

‘current schema’ is the schema that comes first in the ‘search_path’. You can view the search_path using below command:

show search_path();

Search_path can be set for :

  • a session
  • a database
  • a role
  • a role in a database

You can modify the search_path within a session using:

set search_path=schema1,schema2,public;

To set search_path for a database, use the below command:

alter database dbname1 set search_path=schema1,schema2,public;

Use the below command to update the search path for a user, :

alter role user1 set search_path=schema1,schema2,public;

You can also set a search_path for a user account within a database as below:

alter role user1 in database dbname1 set search_path=schema1,schema2,public;
See also  How to drop a constraint in Redshift database