PostgreSQL import SQL file

In this Postgresql tutorial, we will learn about “Postgresql import SQL file” using different methods and platforms with multiple examples. We will create and insert some data into the PostgreSQL database using the SQL file or by importing the SQL file.

PostgreSQL import SQL file

Prerequisites

Before you start, ensure you have:

Prerequisite	Description
PostgreSQL Installed	Make sure PostgreSQL server is installed on your Windows or Linux machine.
Access Credentials	You need a username and password with sufficient privileges to create or modify databases.
SQL File Ready	Have your .sql file available locally or on the server where PostgreSQL is installed.
Command Line or GUI Access	Access to either the terminal (psql) or pgAdmin 4 for GUI-based import.

When we want to create a database or add tables to an existing database using SQL files.

To create a database, we may have created an SQL file that contains the command to restore the database; for that, we will use the already created SQL file.

Let’s examine each approach individually to achieve this goal.

Method 1: Using Command Line Tool

In PostgreSQL, we use the psql command to import SQL files or databases.

Psql provides several flags or options that we can use with the psql command to connect to different databases, users, and hosts of the PostgreSQL server.

Some of the most common flags or options are:

1. -U username: username of the database that you want to connect.
2. -d dbname: name of the database to connect to.
3. -h hostname: name of the host machine on which the PostgreSQL server is running.

Let’s connect to the PostgreSQL database and import the SQL file using the command line.

• Open the Command Prompt (cmd) on your machine and follow the instructions below.

Before importing an SQL file, you must have an empty database. To create a database named postdata, use the command below.

CREATE DATABASE postdata;

• Check the list of all databases using \l and type \q to exit from the PostgreSQL command-line tool.

• Now we have created a database “postdata”. Let’s import an SQL file ( database.sql that contains a command to create a table named “new” into the newly created database “postdata”.

psql -h localhost -U postgres -d postdata -f D:\Backup\database.sql

• Log in to your database using the command below.

psql -U postgres -d postdata

• Then list the table that you have created in the postdata database using \dt.

postdata=# \dt

I got the expected output after executing the above command as shown in the screenshot below.

As shown in the output above, we have created a new table by importing a pre-created SQL file into the PostgreSQL database.

Read: PostgreSQL generate_series

Method 2: Importing SQL Files with pgAdmin 4

To import the SQL file using pgAdmin, follow the instructions below.

• Open pgAdmin application and select the database.

• Right-click on the selected database and click on Query Tool.

• After clicking on Query Tool, the SQL Editor Panel appears beside the Browser section of the pgAdmin application.

• Then click on the small folder icon in Query Toolbar of The SQL Editor Panel to import or upload the SQL file.

• Now, select the SQL file or navigate to the folder where the SQL file exists and click on the SELECT button in the bottom right corner.

• Click on small play-icon in Query Toolbar or press F5 from your keyboard to execute the query that appears in Query Editor after importing the SQL file.

Well, we have successfully imported the SQL file using pgAdmin. Let’s view the created table using the command below.

SELECT * FROM new; -- new is the name of table

Read: PostgreSQL cast int

Method 3: Using Docker

When importing an SQL file using Docker, ensure your system has Docker installed. If not, refer to the official documentation on the Docker website, then return.

First, create the PostgreSQL database using Docker Compose. If you’re unsure, follow the instructions below.

• Create a Docker Compose file in your root folder. This file is a configuration file for running PostgreSQL in Docker. The file is called docker-compose.yml.

• Configure the Postgres docker-compose file. We will use the PostgreSQL image available on Docker Hub. There are two things that we need to put in the configuration file.

1. Import the Postgres image in Docker compose
2. Configure the database according to your needs and use it on your computer.

Open the docker-compose.yml file and paste the below instructions:

# A Docker Compose must always start with the version tag.
# We use '3' because it's the last version.
version: '3'

# You should know that Docker Compose works with services.
# 1 service = 1 container.
# For example, a service, a server, a client, a database...
# We use the keyword 'services' to start to create services.

services:

  # The name of our service is "database"
  # but you can use the name of your choice.
  # Note: This may change the commands you are going to use a little bit.


  database:
    # Official Postgres image from DockerHub (we use the last version)

    image: 'postgres:latest'
    restart: always

    # By default, a Postgres database is running on the 5432 port.
    ports:
      - 5432:5432

    environment:
      POSTGRES_USER: postgres # The PostgreSQL user
      POSTGRES_PASSWORD: 12345 # The PostgreSQL password 
      POSTGRES_DB: default_database # The PostgreSQL default database 

Now, you can run the database and connect to it.

Let’s run the Dockerfile using the command below in the terminal.

docker-compose up

At this point, after running the above command, the container and image file are created in Docker.

Time to import the SQL file, so create SQL file name data.sql in your root directory with commands.

In your current terminal, type nano data.sql to create the file and write the below command in that file.

data.sql --file name where we will write the below commands

CREATE TABLE new(id INT,name VARCHAR);

INSERT INTO new(id,name)values(1,"Travis");

SQL file created successfully. Let’s import the file using Docker. Use the commands below.

docker container exec -i kumar_saurabh_database_1 psql -U postgres default_database < data.sql

• Now, open the Docker application from your desktop, which you installed following the official documentation for Docker installation, and select a container from the application.

• After that, click on the CLI (Command-Line Tool) icon of the container, and it will launch the command-line tool, which allows you to interact with the Docker daemon.

• In the Docker container command-line tool, verify the imported sql file that contains commands to create a new table in the database using the following commands.

psql -U postgres -d default_database -- login into database



/dt -- to list all the tables in current database



SELECT * FROM new; -- to show all the data or records in the table

As shown in the output above, we have successfully imported the SQL file using Docker and created a new table with sample data.

Read: Psql import SQL file permission denied

Method 4: Importing SQL file using Heroku CLI

In Heroku, to import the SQL file, we first need to install the Heroku CLI on our system. After installing the Heroku CLI, we also need to install the PostgreSQL database from the official Heroku website. Then, come back here.

Use the below instructions :

• Open your terminal and log in to your Heroku account using the command below.

heroku login

• After logging in, go to the Heroku website where you created the database, following the official instructions, and click on the name of your database.

• Then, click on the Settings tab and select View Credentials.

• Note down or copy the Heroku CLI command and paste it into your terminal or command-line to log in to the database.

• Now, we have logged into the database. Let’s import the SQL file.

We have successfully imported the SQL file using Heroku and also created a new table.

Conclusion

Importing SQL files into PostgreSQL is a crucial skill for anyone managing databases in the United States. Whether you prefer the speed and flexibility of the command line with psql, the visual ease of pgAdmin 4, or Docker to get the job done efficiently.

So in this PostgreSQL tutorial, we have learned about “PostgreSQL import SQL file” using multiple approaches.

You may also like reading the following articles.

• PostgreSQL date_trunc function
• PostgreSQL function return table
• PostgreSQL TO_NUMBER() function 
• PostgreSQL TO_TIMESTAMP function
• How to import CSV file into PostgreSQL