How to Run SQL File into a Specific Schema in PostgreSQL Database Sever

By Posted on

Introduction

This article is an article where the focus is to run an SQL file into a specific schema in the PostgreSQL database server. Actually, the SQL file is just a bunch of insert statement compilations to a certain table. But the problem exist when the execution of the SQL file ends in a failure attempt. 

C:\Users\Personal>psql -Upostgres -d employee < "C:\Users\Personal\Downloads\insert-output.sql" 
Password for user postgres: 
... 
ERROR: relation "output" does not exist 
LINE 1: INSERT INTO "output" ("transaction_id", "year", "month", "target... ^ 
ERROR: relation "output" does not exist 
LINE 1: INSERT INTO "output" ("transaction_id", "year", "month", "target... ^ 
C:\Users\Personal>

The above command execution ends in failure. It is because the target table for the insert query does not exist. It means, the default current active working schema does not have that ‘output’ table. So, before executing the above command, just try to check the schema first. In this context, checking whether the table ‘output’ exist in that default schema. 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 default current working active schema.

 

Solve error by switching or changing to a specific schema in PostgresQL database server

The failure of the above command execution of the SQL file is because there are no tables with the name of ‘output’ at all. It is because one possible cause which can actually happen. It is because the execution of the SQL file is not in the right schema. Basically, it is very difficult to pass a schema target in the psql command line connection. In order to solve it, the following is the actual step by switching or changing to a specific schema :

  1. First of all, connect to the PostgreSQL command console. 

    C:\Users\Personal>psql -Upostgres
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.

postgres=#

  2. After successfully logging in to the PostgreSQL command console, just check the available schema by typing the following command : 

    employee=# \dn
List of schemas
Name          | Owner
--------------+----------
assessment    | postgres
career        | postgres
training      | postgres
performance   | postgres
profile       | postgres
public        | postgres
training      | postgres
(7 rows)employee=#

  3. Continue on after, for each and every schema, just check the table. Since there is a specific table name already, just pass the table name to the command. It will search for the specific table with the name. For the sake of keeping it short, it will just try to inspect the correct schema where the table exist. That schema is the ‘performance’ schema as in the query execution below. In this example, the table name is ‘output’, so the command execution exist as follows : 

    employee=# set search_path to performance;
SET
employee=# \dt+ output;
List of relations
Schema      | Name   | Type  | Owner    | Size  | Description
------------+--------+-------+----------+-------+-------------
performance | output | table | postgres | 16 kB |
(1 row)

employee=#

    In order to make it permanent, just perform alter role and set search_path to the schema. Just read the article with the title of ‘How to Change Default Current Active Working Schema Permanently in PostgreSQL Database Sever’ in this link to have more information about it. Execute the following query or command to make it permanent : 

    employee=# alter role postgres set search_path to performance;
ALTER ROLE
employee=# \q
  4. After successfully change the default current working schema from ‘public’ to ‘performance’ where the target table ‘output’ exist, execute the command again for running the SQL file as follows : 
    C:\Users\Personal>psql -Upostgres -d employee < "C:\Users\Personal\Downloads\insert-output.sql"
Password for user postgres:
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
...

    C:\Users\Personal>

Leave a Reply