Write Data From Sql File Upload Back to Dekstop
In this Postgresql tutorial, we volition learn almost "Postgresql import SQL file" using different methods and platforms, we will create and insert some information into the Postgresql database using the SQL file or by importing the SQL file.
We are going to cover the post-obit topics:
- Postgresql import SQL file command line
- Postgresql import SQL file pgadmin four
- 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 nosotros want to create tables in an existing database using SQL files.
To create a database, we may take 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 control line
In Postgresql, we use the psql command to import SQL files or databases.
So "What is psql?." Psql is an interactive-terminal or concluding-based front end-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 tin use with the psql control to connect with different databases, users, and hosts of the Postgresql server.
Some of the most common flags or options are:
- -U username: username of the database that you lot want to connect.
- -d dbname: name of the database to connect to.
- -h hostname: proper name of the host machine on which the Postgresql server running.
Let'south connect to the PostgreSQL database and import the SQL file using the command line.
- Open the command line or cmd on your car 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 control-line tool.
- Now we take created a database "postdata", permit'southward import an SQL file ( database.sql that contains a command to create tabular array name "new" ) into the newly created database "postdata".
psql -h localhost -U postgres -d postdata -f D:\Fill-in\database.sql - And login into your database using the beneath command.
psql -U postgres -d postdata - Then listing the table that you have created in the postdata database using \dt.
postdata=# \dt 
As we can run across in the above output, we take created a new table past importing a pre-created SQL file into the Postgresql database.
Read: Postgresql generate_series
Postgresql import SQL file pgadmin iv
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.
- Subsequently clicking on Query Tool, The SQL Editor Panel appears beside the Browser section of the pgAdmin application.
- And so click on small binder-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 push button at bottom correct-corner.
- Click on small play-icon in Query Toolbar or printing F5 from your keyboard to execute the query that appears in Query Editor later on importing the SQL file.
Well, we accept successfully imported the SQL file using pgadmin, allow' encounter the created tabular array using the below command.
SELECT * FROM new; -- new is the proper noun of table 
Read: Postgresql bandage int
Postgresql import SQL file docker
When we desire to import an SQL file using docker, your system must have a docker installed, if not, become to the official documentation of the docker website and then come back.
Outset, create the Postgresql database using docker-compose, if you don't know so 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 bachelor on the docker hub, there are two things that we demand to put in the configuration file.
- Import the Postgres image in Docker compose
- 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 starting time with the version tag. # We use '3' because it'due south the last version. version: 'three' # Y'all should know that Docker Compose works with services. # one service = 1 container. # For instance, a service, a server, a client, a database... # We use the keyword 'services' to showtime to create services. services: # The name of our service is "database" # but yous tin utilize the name of your choice. # Note: This may modify the commands you lot are going to utilise a little fleck. database: # Official Postgres paradigm from DockerHub (nosotros apply the final version) image: 'postgres:latest' restart: always # By default, a Postgres database is running on the 5432 port. ports: - 5432:5432 environs: 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 information technology.
Allow'southward run the docker file using the below command in the final.
docker-etch upwards 
At this signal, after running the higher up command information technology creates the container and epitome file in docker.
Time to import the SQL file, and then create SQL file proper 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 control in that file.
data.sql --file proper name where nosotros will write the below commands CREATE TABLE new(id INT,name VARCHAR); INSERT INTO new(id,proper noun)values(1,"Travis"); 
SQL file created successfully, permit's import the file using docker, use the beneath commands.
docker container exec -i kumar_saurabh_database_1 psql -U postgres default_database < data.sql 
- Now open the Docker awarding from your desktop, that you take installed while following official documentation for installing docker, select container from the awarding.
- 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 
Every bit we tin can see in the above output, we take successfully imported the SQL file using docker and created a new tabular array with some data in it.
Read: How to detect primary column name in Postgresql
Psql import SQL file permission denied
Sometimes when we import the SQL file we go error permission denied, outset allow's see the mistake while importing the SQL file.
For Windows users, Enter into psql command prompt by using the below command, if it asks for a countersign enter the password for the user of psql.
psql -U postgres Now, recall that we accept an SQL file on our computer somewhere like D:\Backup\proper name.sql and we want to import that file, for that use the below control.
\i D:\Backup\proper noun.sql After running the to a higher place control, we get an error Permission denied.
To solve the error, utilise the double slashes ( \\ ) in the identify of single slashes ( \ ) and wrap the path of the SQL file within a single quotation mark ( ' ' ), employ the beneath control.
\i 'D:\\Fill-in\\proper name.sql' 
In the above output, we take 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 control:
sudo su -l postgres -- afterwards the above command enter the psql command psql -- and 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 unmarried quotation marker ( ' ' ), use the beneath command.
Now, we accept also solved the mistake 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 afterward installing Heroku CLI, also install the Postgresql database from the official website of Heroku and so come back hither.
Employ the below instructions :
- Open your last and login into your Heroku account using the beneath command.
heroku login 
- Subsequently login, Go to the Heroku website where you accept created the database while following the in a higher place official website and click on the name of your database.
- Then click on the Setting tab and click on View Credentials.
- Note downwardly or copy the Heroku CLI command and paste information technology into your terminal or command-line to login into the database.
- Now, We have logged into the database, allow' import the SQL file.
We have successfully imported the SQL file using Heroku and also created a new table.
You may as well like reading the following articles.
- Postgresql date_trunc part
- Postgresql function return table
- PostgreSQL TO_NUMBER() function
- PostgreSQL TO_TIMESTAMP function
- How to import CSV file into PostgreSQL
- Postgresql automobile increment
- Postgresql row_number
- How to migrate from MySQL to Postgres
- Postgresql current_timestamp
And then in this Postgresql tutorial, we have learned virtually "Postgresql import SQL file" using a different approach.
Nosotros have covered the following topics:
- Postgresql import SQL file control 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
Afterwards working for more than 15 years in the Software field, especially in Microsoft technologies, I take decided to share my adept noesis of SQL Server. Check out all the SQL Server and related database tutorials I take shared here. Most of the readers are from countries like the U.s. of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am besides a Microsoft MVP. Check out more here.
Source: https://sqlserverguides.com/postgresql-import-sql-file/
0 Response to "Write Data From Sql File Upload Back to Dekstop"
Post a Comment