The default schema in PostgreSQL is “public“. Some time you need to change it for some raisons (Example Migration of DB)
Before start to change, you have to check what is current PostgreSQL default schema ? for that run this command :

SHOW search_path

check the DB config


#---------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#---------------------------------------------------------------------------
 
# - Statement Behavior -
 
#search_path = '"$user",public'		# schema names
#default_tablespace = ''		# a tablespace name, '' uses
                    # the default
#check_function_bodies = on
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = off

to set the new schema , use this command :

SET search_path = new_schema

This command is apply to current session only, next time schema will change back to public. If we want to make effect permanently, we have to change in postgresql.conf file like following.

#---------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#---------------------------------------------------------------------------
 
# - Statement Behavior -
 
#search_path = '"$user",public'		# schema names
search_path = '"$user",new_schema'	# NEW SCHEMA HERE
#default_tablespace = ''		# a tablespace name, '' uses
                    # the default
#check_function_bodies = on
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = off

Restart PostgreSQL service. And its Done 😉

Laisser un commentaire