HOWTO · PostgreSQL
How to Run SQL File in PSQL
This article explains how to run a SQL file direct from terminal/command-line or psql shell. For that, you need to specify the hostname, port, username, and database name.
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.
