How to Change Default Current Active Working Schema Permanently in PostgreSQL Database Sever

By Posted on

Introduction

This article is showing how to change default current active working schema permanently in PostgreSQL database server. By executing a certain command, it will switch or change from the default schema to other schema. In terms of default schema, it is a schema with the name of ‘public’. This article has a connection with the previous article. That article exist in this link with the title of ‘How to Change the Current Working Schema in PostgreSQL Command Line’. It is also an article which is focusing on how to switch schema. In that article, there is a command for switching to a certain schema. But it is rather temporary than permanent change. So, if the user logout from the PostgreSQL Command Line, the change from the previous command for switching schema will not be saved. But in this article, switching to the other schema will have a permanent effect.

Change or Switch Default Current Active Working Schema Permanent in PostgreSQL

So, in order to switch the schema or to keep the change of the schema in a permanent way, just do the following sequence :

  1. First of all, just access the PostgreSQL command line as in the command execution below : 

    C:\Users\Personal>psql -Upostgres -d employee
Password for user postgres:
psql (12.2)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

employee=#

  2. After successfully logging in, just check what is the default current working active schema. It is important since there is a default current working schema active depend on the user and the database given in the psql command execution. The above psql command execution is using ’employee’ as its database and ‘postgres’ as its user for further connection. By default, the schema name is ‘public’. Just read the article with the title of ‘How to Check Default Current Active Working Schema in PostgreSQL Database Server’ in this link to check the active schema. It goes as the following command execution : 

    employee=# show search_path;
search_path
-------------
public
(1 row)

employee=#

  3. Next, after successfully logging in, instead of running set search_path to schema_name, just alter the user role. It will implement the default schema  permanently. Below is the command pattern or the query pattern to achieve it : 

    alter role role_name set search_path to schema_name

    The following is the command or query execution according to the above command or query pattern : 

    employee=# alter role postgres set search_path to performance;
ALTER ROLE
employee=# \q
  4. Logout or quit from the PostgreSQL command console and right after that just login again. After successfully logging in, check the default current working active schema as follows : 
    employee=# \q
C:\Users\Personal>psql -Upostgres -d employee
Password for user postgres:
psql (12.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.

employee=# show search_path;
search_path
-------------
performance
(1 row)

employee=#

    The above output command or query execution prove that altering the search_path of a user will affect permanently. Although the user perform logout or disconnect from the PostgreSQL database server, right after the moment login or connect it once more to the PostgreSQL database Server, the default current working active schemas is still changing. It change from ‘public’ to ‘performance’.

Leave a Reply