Alter default search path of user

With the below commands, user’s default search_path can be set.

Check search path before set default schema.

postgres=# \c postgres user1
You are now connected to database "postgres" as user "user1".
postgres=> show search_path;
  search_path   
----------------
 "$user",public
(1 row)

Set search_path with alter user command to append a new schema my_schema

postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# alter user user1 set search_path='my_schema, "$user", public';
ALTER ROLE

Check result after execution.

postgres=# \c postgres user1
Password for user user1: 
You are now connected to database "postgres" as user "user1".
postgres=> show search_path;
 search_path 
-------------
 my_schema, "$user", public
(1 row)

Alternative:

postgres=# set role user1;
postgres=# show search_path;
 search_path 
-------------
 my_schema, "$user", public
(1 row)

Found a mistake? Have a question or improvement idea? Let me know.

Table Of Contents