Postgresql import SQL file + Examples

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

We are going to cover the following topics:

• Postgresql import SQL file command line
• Postgresql import SQL file pgadmin 4
• Postgresql import SQL file docker
• Postgresql import SQL file windows
• psql import SQL file permission denied
• Heroku Postgres import SQL file

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

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

Postgresql import SQL file command line

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

So “What is psql?.” Psql is an interactive-terminal or terminal-based front-end that enables us to type in queries and send those queries to the Postgresql database interactively.

Psql provides the number of flags or options that we can use with the psql command to connect with 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 running.

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

• Open the command line or cmd on your machine and follow the below instructions.

Before importing an SQL file, you must have an empty database so create database name postdata using the below command.

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 table name “new” ) into the newly created database “postdata”.

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

• And login into your database using the below command.

psql -U postgres -d postdata

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

postdata=# \dt

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

Read: Postgresql generate_series

Postgresql import SQL file pgadmin 4

To import the SQL file using pgAdming follow the below instructions.

• 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 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 the folder where SQL file exist and click on SELECT button at 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’ see the created table using the below command.

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

Read: Postgresql cast int

Postgresql import SQL file docker

When we want to import an SQL file using docker, your system must have a docker installed, if not, go to the official documentation of the docker website then come back.

First, create the Postgresql database using docker-compose, if you don’t know then follow the below instructions.

• Create the docker-compose file in your root folder, this file is a configuration file to run the Postgresql in docker, the file is called docker-compose.yml.

• Configure the Postgres docker-compose file, we are going to the image for Postgresql available on the 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 need and use it on 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 docker file using the below command in the terminal.

docker-compose up

At this point, after running the above command it creates the container and image file 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 below commands.

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

• Now open the Docker application from your desktop, that you have installed while following official documentation for installing docker, select container from the application.

• After then click on the CLI ( Command line tool ) icon of the container and it will launch the command-line tool that lets you talk to the Docker daemon.

• In the docker container command-line tool, to verify the imported sql file that contains commands to create a new table in the database using the below 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 we can see in the above output, we have successfully imported the SQL file using docker and created a new table with some data in it.

Read: How to find primary column name in Postgresql

Psql import SQL file permission denied

Sometimes when we import the SQL file we get error permission denied, first let’s see the error while importing the SQL file.

For Windows users, Enter into psql command prompt by using the below command, if it asks for a password enter the password for the user of psql.

psql -U postgres

Now, think that we have an SQL file on our computer somewhere like D:\Backup\name.sql and we want to import that file, for that use the below command.

\i D:\Backup\name.sql

After running the above command, we get an error Permission denied.

To solve the error, use the double slashes ( \\ ) in the place of single slashes ( \ ) and wrap the path of the SQL file within a single quotation mark ( ‘ ‘ ), use the below command.

\i 'D:\\Backup\\name.sql'

In the above output, we have imported the SQL file successfully, created the table with some data in it, and solved the problem Permission denied.

For Linux users, Login into Postgressql as a Postgres user to access the databases and then enter into psql prompt using the below command:

sudo su -l postgres 

-- after the above command enter the psql command

psql

-- then enter the below command to import the SQL file

\i '//home//saurabh//database.sql

Use the double slashes ( // ) in the place of single slashes ( / ) and wrap the path of the SQL file within a single quotation mark ( ‘ ‘ ), use the below command.

Now, we have also solved the error for Linux systems.

Read: Update query in PostgreSQL

Heroku Postgres import SQL file

In Heroku, to import the SQL file first, we need to install Heroku CLI on our system, and after installing Heroku CLI, also install the Postgresql database from the official website of Heroku then come back here.

Use the below instructions :

• Open your terminal and login into your Heroku account using the below command.

heroku login

• After login, Go to the Heroku website where you have created the database while following the above official website and click on the name of your database.

• Then click on the Setting tab and click on View Credentials.

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

• Now, We have logged into the database, let’ import the SQL file.

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

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
• Postgresql auto increment
• Postgresql row_number
• How to migrate from MySQL to Postgres
• Postgresql current_timestamp

So in this Postgresql tutorial, we have learned about “Postgresql import SQL file” using a different approach.

We have covered the following topics:

• Postgresql import SQL file command line
• Postgresql import SQL file pgadmin 4
• Postgresql import SQL file docker
• Postgresql import SQL file windows
• psql import SQL file permission denied
• Heroku Postgres import SQL file