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.sqlAdd 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 tableCREATE 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.
