Thread: How to execute the sql file in PSQL
Stackoverflow question link: https://stackoverflow.com/questions/64210281/using-psql-executing-sql-format-file-shows-permission-denied-on-windows-platform
I followed one PostgreSQL tutorial step by step. One session to use PSQL to execute sql files to create a new database in PostgreSQL.
- copy paste the sql file within "C:\Program Files\PostgreSQL\12" directory.
- execute the following code \i C:\\Program Files\\PostgreSQL\\12\\demo-big-en-20170815.sql
The message shows C:: Permission denied
then tried \ir "C:\\Program Files\\PostgreSQL\\12\\demo-big-en-20170815.sql"
Failed
The message shows unrecognized win32 error code: 123"C:/Program Files/PostgreSQL/12/createdatabasesupertest.sql: Invalid argument
SQL file probably was wrong. SO I tried to create a simple sql file (create a new database). Then I follow the above mentioned step to execute the file on windows PSQL. But it failed the same way just like the above mentioned.
So basically my PSQL can not run script files. What should i do?
On 10/5/20 7:55 AM, Mark wrote: > > > Stackoverflow question link: > https://stackoverflow.com/questions/64210281/using-psql-executing-sql-format-file-shows-permission-denied-on-windows-platform > > > I followed one PostgreSQL tutorial step by step. One session to use PSQL > to execute sql files to create a new database in PostgreSQL. > > 1. copy paste the sql file within "C:\Program Files\PostgreSQL\12" > directory. > 2. execute the following code \i C:\\Program > Files\\PostgreSQL\\12\\demo-big-en-20170815.sql > > Failed > The message shows C:: Permission denied > then tried \ir "C:\\Program > Files\\PostgreSQL\\12\\demo-big-en-20170815.sql" > Failed > The message shows unrecognized win32 error code: 123"C:/Program > Files/PostgreSQL/12/createdatabasesupertest.sql: Invalid argument > > SQL file probably was wrong. SO I tried to create a simple sql file > (create a new database). Then I follow the above mentioned step to > execute the file on windows PSQL. But it failed the same way just like > the above mentioned. > > So basically my PSQL can not run script files. What should i do? The issue is the user you are running psql as does not have permissions to read the file. This is a OS permissions thing. Either run psql as a user that can read the file or change the permissions on the file to those that allow the psql user to read it. > -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 10/5/20 7:55 AM, Mark wrote:
>> I followed one PostgreSQL tutorial step by step. One session to use PSQL
>> to execute sql files to create a new database in PostgreSQL.
>> 1. copy paste the sql file within "C:\Program Files\PostgreSQL\12"
>> directory.
>> 2. execute the following code \i C:\\Program
>> Files\\PostgreSQL\\12\\demo-big-en-20170815.sql
>>
>> Failed
>> The message shows C:: Permission denied
> The issue is the user you are running psql as does not have permissions
> to read the file. This is a OS permissions thing. Either run psql as a
> user that can read the file or change the permissions on the file to
> those that allow the psql user to read it.
... or more likely, put the SQL file in a saner place. Dropping random
files into a program directory can only lead to trouble. You should
treat such directories as read-only unless you know exactly what you
are doing.
It seems likely to me that this failure stems from PG being installed
with permissions settings that prevent it from reading/modifying its own
executables, which is good solid security practice.
(If the tutorial actually told you to do that, the tutorial's author
is utterly clueless.)
regards, tom lane
Actually, a single quotation will work.
\ir 'C:\\Program Files\\PostgreSQL\\12\\demo-big-en-20170815.sql'But I don't know why a single quotation will work.It would be very helpful if you guys can explain to me.
On Mon, Oct 5, 2020 at 9:31 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 10/5/20 7:55 AM, Mark wrote:
>> I followed one PostgreSQL tutorial step by step. One session to use PSQL
>> to execute sql files to create a new database in PostgreSQL.
>> 1. copy paste the sql file within "C:\Program Files\PostgreSQL\12"
>> directory.
>> 2. execute the following code \i C:\\Program
>> Files\\PostgreSQL\\12\\demo-big-en-20170815.sql
>>
>> Failed
>> The message shows C:: Permission denied
> The issue is the user you are running psql as does not have permissions
> to read the file. This is a OS permissions thing. Either run psql as a
> user that can read the file or change the permissions on the file to
> those that allow the psql user to read it.
... or more likely, put the SQL file in a saner place. Dropping random
files into a program directory can only lead to trouble. You should
treat such directories as read-only unless you know exactly what you
are doing.
It seems likely to me that this failure stems from PG being installed
with permissions settings that prevent it from reading/modifying its own
executables, which is good solid security practice.
(If the tutorial actually told you to do that, the tutorial's author
is utterly clueless.)
regards, tom lane
Hi Mark, > On 06. Oct, 2020, at 10:23, Mark <jxustnc@gmail.com> wrote: > > Actually, a single quotation will work. > \ir 'C:\\Program Files\\PostgreSQL\\12\\demo-big-en-20170815.sql' > But I don't know why a single quotation will work. > It would be very helpful if you guys can explain to me. because Microsoft had the glorious idea of putting a blank character into "Program Files". Quoting this will make the wholeC:\...sql line one single word which is then passed to \i. Cheers, Paul