The Problem
Suppose you download an sql file containing some sql instructuions (such as dummy data from mockaroo), the options you have to run these commands in postgres include copying the commands in the file over to the psql shell manually and running them. This approach is workable if the file contains only a few commands, however, say the file contains hundreds of lines of sql commands, you realize this option doesn't scale with size.
The Solution
Thankfully, you can execute SQL commands located in another file on your pc straight from the psql shell. The command to do this is \i filepath to sql file
To get the filepath.
- Open the file in a code editor such as VSCode
- Right click on the file.
Select the copy path option from the dropdown menu.
Then, open your psql shell and run this command
\i filepath to sql file, and it’s more than likely you’ll get the following output:
The solution to the above is quite simple really, very simple infact.
Let’s troubleshoot
It may be this file was downloaded onto your pc and needs to have necessary permissions.
- Open the file location
Right-click on the file and click on properties in the drop down menu
Click on unblock just near the lower right edge of the properties dialogue box and select the apply option (it will become clickable)
Now that we’ve unblocked the file, we can run the command again in our psql shell and see what happens.
Seems like we're still getting the same error as before. What could be the issue?
The Solution
I did mention that the solution is simple, and it is quite simple, to the initiated. The uninitiated might spend hours or days of debugging trying to figure out what the problem is. You may even give up at some point and just manually copy the sql commands and run them directly in the psql shell. Well, let's save ourselves any ache:
The simplicity of the solution is quite interesting. All we need to do is simply change the direction of our slashes from “\” to “/” and the command works as shown below.
The solution to this problem stems from how the file paths in windows differs from what the psql shell expects and all we need to do is conform to what psql wants, and that my folks is the solution.
This article does assume that:
The user on the psql shell has access to the sql file we are trying to execute code from. Otherwise, all of the above might not apply and it may be necessary to get permission to access the file in the first place.
Kindly share this article if you found it useful. Cheers 😊
Top comments (1)
Very insightful information 🤝