(678) 345-3456
380 Albert St, Melbourne, Australia

Blog Details

  • Stormatics
  • Blog
  • How to Execute Queries from SQL files in PostgreSQL using psql

How to Execute Queries from SQL files in PostgreSQL using psql

Why SQL files are used

In PostgreSQL, there are several ways to execute queries, and one of them is by executing queries from SQL files. This approach allows users to manage and store their SQL queries separately and make debugging and development simpler. Using SQL files also helps in replication of database schemas. This blog discusses how to execute queries from SQL files in PostgreSQL.

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 (
    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.

Comments (4)

  • Andreas Kretschmer

    August 10, 2023 - 5:38 pm

    storing the age of a person as integer isn’t a smart idea…

    • Seba

      August 11, 2023 - 12:46 am


      • Salman Ahmed

        August 11, 2023 - 4:50 pm

        Using an integer to store age may not be a smart idea due to a couple of reasons. Firstly, since age is a constantly changing value, storing it as an integer might not be a wise choice. Secondly, using integers for age doesn’t take months and days into consideration, which could be very important in some cases.

    • Salman Ahmed

      August 11, 2023 - 2:33 pm

      Thank you for your insightful feedback! You are indeed right that using integer to store age may not always be the smartest option, but the queries in the SQL file were meant as illustrative examples.

      Stay tuned for upcoming blogs on data types and more at Stormatics!

Leave A Comment