Follow dba-ninja.com

Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions

dba-ninja.com Links

Dba_db2_button

Sqlserver_dba_button

How to change PostgreSQL default schema

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


Author: Rambler (http://www.dba-ninja.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

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.

Working...

Post a comment on How to change PostgreSQL default schema


dba-ninja.com