How to Run SQL File in PSQL

Shihab Sikder Feb 02, 2024
  1. Run SQL File From the Command Line or Terminal
  2. Run SQL Script in the Command-Line
  3. Check From the PSQL Shell
  4. Run SQL File From PSQL Shell
How to Run SQL File in PSQL

Sometimes, you might need to run many queries at a time, say CRUD operations. For that, it’s difficult to use the command line or terminal to write the SQL command correctly.

Write it to a file and save it with the extension .sql if you know the query. It defines that the content inside the file is Standard Query Language.

Run SQL File From the Command Line or Terminal

You want to create a table and insert some data into the table.

CREATE TABLE BANK(
    ID INT PRIMARY KEY,
    BANK_NAME VARCHAR,
    SWIFTCODE VARCHAR NOT NULL
);

CREATE TABLE ACCOUNT(
    ID INT PRIMARY KEY,
    ACCOUNT_NAME VARCHAR NOT NULL,
    BANK_ID INT,
    BALANCE INT DEFAULT 0,
    CONSTRAINT fk_bank FOREIGN KEY(BANK_ID) REFERENCES BANK(ID)
);
INSERT INTO bank(ID, BANK_NAME,SWIFTCODE) VALUES(1,'State Bank','123456');
INSERT INTO bank(ID, BANK_NAME,SWIFTCODE) VALUES(2,'Central Bank','654321');
INSERT INTO Account(ID,ACCOUNT_NAME,BANK_ID,BALANCE) VALUES(1,'Jhon',1,500);

The format for running this file is given below.

psql -h <host_address> -d <database_name> -U "database_user" -p "port_no" -a -q -f "file_path"
Flag Meaning
-h Host address (by default, it’s Localhost)
-d Database Name (default database is postgres)
-U Username (Default username is postgress)
-p Port Number (Default port is 5432)
-a Print everything if the SQL has any printables
-f File directory of the SQL Script

Run SQL Script in the Command-Line

C:\Users\Admin>psql -h localhost -d postgres -U postgres -p 5432 -a -q -f C:\Users\Admin\Desktop\script1.sql
Password for user postgres:
CREATE TABLE BANK(
    ID INT PRIMARY KEY,
    BANK_NAME VARCHAR,
    SWIFTCODE VARCHAR NOT NULL
);

CREATE TABLE ACCOUNT(
    ID INT PRIMARY KEY,
    ACCOUNT_NAME VARCHAR NOT NULL,
    BANK_ID INT,
    BALANCE INT DEFAULT 0,
    CONSTRAINT fk_bank FOREIGN KEY(BANK_ID) REFERENCES BANK(ID)
);
INSERT INTO bank(ID, BANK_NAME,SWIFTCODE) VALUES(1,'State Bank','123456');
INSERT INTO bank(ID, BANK_NAME,SWIFTCODE) VALUES(2,'Central Bank','654321');
INSERT INTO Account(ID,ACCOUNT_NAME,BANK_ID,BALANCE) VALUES(1,'Jhon',1,500);

Check From the PSQL Shell

postgres=# \dt
          List of relations
 Schema |   Name   | Type  |  Owner
--------+----------+-------+----------
 public | account  | table | postgres
 public | accounts | table | postgres
 public | bank     | table | postgres
 public | logger   | table | postgres
 public | randoms  | table | postgres
 public | students | table | postgres
 public | times    | table | postgres
(7 rows)

postgres=#

Run SQL File From PSQL Shell

Also, you can run the SQL script from the psql shell, like the following:

postgres-# \i C:/Users/Admin/Desktop/script1.sql
CREATE TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1

Here, you can see that two tables have been created, and three insert commands were executed. Sometimes in Windows, you may get Permission Denied.

That basically will happen for the backslashes in the directory path after \i.

Shihab Sikder avatar Shihab Sikder avatar

I'm Shihab Sikder, a professional Backend Developer with experience in problem-solving and content writing. Building secure, scalable, and reliable backend architecture is my motive. I'm working with two companies as a part-time backend engineer.

LinkedIn Website