How to Change the Current Working Schema in PostgreSQL Command Line

Posted on

Introduction

This article will show how to do a specific thing in PostgreSQL database. Using a certain command, it will show how to switch between schemas. Basically, a database has one default schema. That is the ‘public’ schema. What if there are more than one schema ?. Let’s say there is another schema with another name. Basically, the default connection string in the PostgreSQL database server will have a database name. But if there is no argument for specifying the schema name, there is a solution for that. Just execute a certain command after successfully logging in to the PostgreSQL database server.  This article will demonstrate how to do it.

Switch Current Active Schema using Search_Path Command in PostgreSQL Command Line

In order to achieve the purpose for switching to another schema from the default public schema, just execute the command using search_path. The following is the demonstration where everything starts from the logging access as follows :

  1. Accessing the PostgreSQL Database Server from the command line by typing the following command

    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 to the PostgreSQL command console, just check the default current active working schema. Read the article with the title of ‘How to Check Default Current Active Working Schema in PostgreSQL Database Server’ in this link for a reference.
  3. But by default, the current active schema is the public schema. Suppose there will be a need to list all the available tables in another schema, it will generate an error message. Suppose there is another schema with the name of ‘performance’. In that schema there are several tables for an example a table with the name of ‘output’. Selecting the records or the data from a different schema where it is currently active will end in a failure as in the following output :

    employee=# select * from output;
    ERROR: relation "output" does not exist
    LINE 1: select * from output;
    ^
    employee=#
    
  4. In order to solve the problem on the other for selecting the table in another schema, just execute a certain command. That command is the search_path command. The following is the command pattern :

    set search_path to 'schema_name'

    The following is the execution to implement the above command pattern to select the table further :

    employee=# set search_path to performance;
    SET
    employee=#
    
  5. Last but not least, execute the select from table command as follows :

    employee=# select * from output;
    -[ RECORD 1 ]-------------------+------------------------------------
    id | 1
    transaction_id | 249
    month | 1
    year | 2020
    target |
    achievement | 20
    achievement_explanation | 
    achievement_obstacles | 
    plan | 
    create_date |
    create_by |
    update_date |
    update_by |
    
    employee=#
    

    As in the output above, the execution from the table with the name ‘output’ is a success. The reason is because the currently active as a working schema is the ‘performance’ schema. It is not the default ‘public’ schema anymore. Further information also exist in the StackOverFlow site in this link and also in this link.

Leave a Reply