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;