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.Table of ContentsPostgreSQL import SQL filePrerequisitesBefore you start, ensure you have:PrerequisiteDescriptionPostgreSQL InstalledMake sure PostgreSQL server is installed on your Windows or Linux machine.Access CredentialsYou need a username and password with sufficient privileges to create or modify databases.SQL File ReadyHave your.sqlfile available locally or on the server where PostgreSQL is installed.Command Line or GUI AccessAccess 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 havecreatedanSQL filethat 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 ToolIn PostgreSQL, we use thepsqlcommand to import SQL files or databases.Psql provides severalflagsoroptionsthat we can use with the psql command to connect to differentdatabases,users, andhostsof thePostgreSQL server.Some of the most common flags or options are:-Uusername: username of the database that you want to connect.-ddbname: name of the database to connect to.-hhostname: 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 namedpostdata,use the command below.CREATE DATABASE postdata;Check the list of all databases using\land type\qto exit from the PostgreSQL command-line tool.Now we have created a database“postdata”. Let’s import an SQL file(database.sqlthat 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.sqlLog in to your database using the command below.psql -U postgres -d postdataThen list thetablethat you have created in thepostdatadatabase using\dt.postdata# \dtI got the expected output after executing the above command as shown in the screenshot below.As shown in the output above, we have created anewtable by importing apre-created SQL fileinto the PostgreSQL database.Read: PostgreSQL generate_seriesMethod 2: Importing SQL Files with pgAdmin 4To 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 onQuery Tool.After clicking on Query Tool, theSQL Editor Panelappears beside theBrowsersection of the pgAdmin application.Then click on the smallfolder iconinQuery Toolbarof The SQL Editor Panel to import or upload the SQL file.Now, select the SQL file or navigate to the folder wherethe SQL fileexists and click onthe SELECTbutton in the bottomright corner.Click on smallplay-iconinQuery Toolbaror pressF5from your keyboard to execute the query that appears inQuery Editorafter 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 tableRead: PostgreSQL cast intMethod 3: Using DockerWhen 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 thePostgreSQLdatabase usingDocker Compose. If you’re unsure, follow the instructions below.Create a Docker Compose file in your root folder. This file is aconfiguration filefor running PostgreSQL in Docker. The file is calleddocker-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.Import the Postgres image in Docker composeConfigure 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 its 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 databaseNow, you can run the database and connect to it.Let’s run the Dockerfile using the command below in the terminal.docker-compose upAt 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 namedata.sqlin your root directory withcommands.In your current terminal, typenano data.sqlto 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.sqlNow, 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 theCLI(Command-Line Tool) icon of thecontainer,and it will launch thecommand-line tool,which allows you to interact with theDocker 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 tableAs 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 deniedMethod 4: Importing SQL file using Heroku CLIIn 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 loginAfter 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 theSettingstab and selectView Credentials.Note down or copy theHeroku CLIcommand 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.ConclusionImporting SQL files into PostgreSQL is a crucial skill for anyone managing databases in the United States. Whether you prefer thespeed and flexibility of the command linewithpsql, thevisual ease of pgAdmin 4, or Dockerto get the job done efficiently.So in this PostgreSQL tutorial, we have learned about“PostgreSQL import SQL file”using multiple approaches.