17 May,2022 by Rambler
Question: I know that “public” is PostgreSQL default scheme. I want to change the default schema name , as there is a procedure to migrate tables into a dedicated schema , separate from the public schema . I don't want the application owners to assign a schema name prefix to existing code code i.e reference using the schema name prefix - SELECT * FROM myschema.myTable ?
Answer: Before you do anything you can check the user's current default schema via the the PostgreSQL command :
SHOW search_path
=> "$user",public
You can change the default schema with this command - but the problem is this will be just for the current session
SET search_path = new_schema
To make it a permanent change , you have a number of options;
1) Change the set_schema at the database level
ALTER DATABASE <database_name> SET search_path TO schema1;
2)Change the set_schema at the user or role level
ALTER ROLE <role_name> SET search_path TO schema1;
ALTER USER <user_name> SET search_path TO schema1;
3) Change the postgresql.conf under the CLIENT CONNECTION DEFAULTS section
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |