PostgreSQL – How to change default schema

“public” is PostgreSQL default scheme, i have to change it because i had migrated a new database data into another new schema call “new_public”.
Before start to change, i have to check what is current PostgreSQL default schema?

1) Command


SHOW search_path

2) Check postgresql.conf


#---------------------------------------------------------------------------
# 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

Here i show how to change Postgresql default schema.


SET search_path = new_schema

However above 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

After that just restart PostgreSQL service. Done.

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter, or befriend him on Facebook or Google Plus. If you like my tutorials, consider make a donation to these charities.

Comments

Leave a Reply

avatar
newest oldest most voted
Wutikrai
Guest
Wutikrai

The schema added must be quot qualified. Here is example

search_path = ‘”$user”,public,”dbSys”‘ # schema names

“dbSys” is new schema just added.

Kamal
Guest
Kamal

There’s no need to set this instance-wide. You can change a single user, e.g.:

ALTER USER myuser SET search_path = scott, new_schema, public;

ARUMUGAM A V
Guest
ARUMUGAM A V

Dear Sir,

Please help me in connecting as other user in Postgres sql script.

Sutiation: I am logging in as postgress creating user and database. I would like to login as the user created in sqlscript.

Advance Thanks.

Rick
Guest
Rick

Obrigado!
Thank you very very much!

You saved my night!!! =D

Greetings from Brazil.

trackback
Setting a permanent search_path, the Right Way « dasz.at – Benutzbare Technologie

[…] Others recommend setting the search_path in the postgresql.conf. Current versions of PostgreSQL can set the search_path permanently on a per-database basis without having to touch system configuration files: […]

Maurizio
Guest
Maurizio

Hi,
can you please explain me how to change default schema in a per-database configuration.
I’ve the same user enabled to connect to different databases and i’d like to dinamically set e default schema for each database.

Thanks
Mauriizo

Atul
Guest
Atul
Hi, I have some different issue. I have created a Database, “DB1” which has more than 1 schema, viz, “Common”, “User” and ofcourse “public”. I have created a LoginRole also, “meUser”. 1. Created DB1, Common, User and tables under Common/User with owner=meUser 2. Given Privileges for all tables inside Common/User to public as well as meUser. 3. Given Priv. and Grants for Schema Common/User to meUser 4. Given Priv. and Grants for DB1 to meUser But now when I use pgAdmin3 to exe “select * from Common.myTable” I get the following error. ERROR: schema “Common” does not exist ********** Error… Read more »
Noni
Guest
Noni

Postgres use lower case for all not-quoted fields on the sql so try using quotes for the schema name i.e. “Common”.table

Hope it helps.