Blog Details

Step 1: Create a SQL File

The first step is to create a SQL file that contains the queries you want to execute in PostgreSQL. It is advised to give it a meaningful name to represent its purpose.

Create SQL file

touch create_fill_table.sql

Add queries to you SQL file

Any text editor or IDE can be used to write to a SQL file. We’ll be adding the following queries to create_fill_table.sql file.

-- Create a new table
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INTEGER
);

-- Insert data into the employees table
INSERT INTO employees (first_name, last_name, age)
VALUES ('John', 'Doe', 30),
       ('Jane', 'Smith', 28),
       ('Michael', 'Johnson', 35);

-- Retrieve all employees
SELECT * FROM employees;

Step 2: Connect to PostgreSQL and Import SQL file

To execute queries from the SQL file, you need to connect to your PostgreSQL database. We will connect to PostgreSQL database using psql command-line utility. Open your terminal or command prompt and run the following command with either < operator or -f flag.

Using input redirection operator <

When input redirection operator < is used, the contents of the SQL file are read and passed as input  to the psql command. It can be considered as if the contents of the file are directly typed into the terminal.

psql -h <host> -d <database> -U <username> -p <port> < create_fill_table.sql

Using -f flag

When -f flag is used, the file to be executed directly by psql is specified. It reads and executes the SQL commands from the file in a batch manner. Using -f flag also enables displaying error messages with line numbers. There are also chances that by using -f flag start-up overhead could be reduced.

psql -h <host> -d <database> -U <username> -p <port> -f create_fill_table.sql

These flags remain the same irrespective of which method you use:
-h: IP address of PostgreSQL server
-d: Database name
-U: PostgreSQL username
-p: PostgreSQL port

Once you have successfully connected to your PostgreSQL database, the next step is to make sure the queries inside the SQL file will be executed and the output of each query will be displayed in the terminal.

Executing queries from files in PostgreSQL is a handy technique that can make database management more organized and efficient. Proper error handling and transaction management are essential to maintain data integrity and consistency, please ensure that the SQL queries in the files are correct and well-tested before executing them against the database.