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 Author
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.
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;
The schema added must be quot qualified. Here is example
search_path = ‘”$user”,public,”dbSys”‘ # schema names
“dbSys” is new schema just added.
Obrigado!
Thank you very very much!
You saved my night!!! =D
Greetings from Brazil.
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 **********
ERROR: schema “Common” does not exist
SQL state: 3F000
I know I have not done something correct in Priv./Grants section because of which this error is coming. But I am not able to figure this out.
Can you help me, in suggesting what might have gone wrong and how can I get this working?
Atul
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.
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.