I am trying to copy a file, but getting error message

user_10865256 0
Viewed 31240

I am new to postgres, probably missing something silly like (the correct name of my directory). Can someone guide me?

I am following book instructions, Practical SQL by Anthony DeBarros

Code:

copy us_counties_2010 from  'C:\Users\obella\OneDrive\Desktop\us_counties_2010.csv' with (FORMAT CSV, HEADER);

Error:

ERROR: could not open file "C:\Users\obella\OneDrive\Desktop\us_counties_2010.csv" for reading: Permission denied HINT: COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's \copy. SQL state: 42501

copy us_counties_2010 from 'C:\Users\obella\OneDrive\Desktop\us_counties_2010.csv' with (FORMAT CSV, HEADER);

Expected:

Query returned successfully: 3143 rows affected

Actual:

ERROR: could not open file "C:\Users\obella\OneDrive\Desktop\us_counties_2010.csv" for reading: Permission denied HINT: COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's \copy. SQL state: 42501

postgresql sql
7 Answers

All that is to be done is: Go to Properties of that particular file by right clicking on it. Then, go to Security tab of the displayed Properties dialog box. Click on Edit option. Permissions dialog box appears, then click on Add button. Type 'Everyone' (without apostrophes) in the "Enter the object names to select" description box and click on OK button. Then, make sure all the checkboxes of "Permissions for Everyone" are selected by just ticking the "Full Control" check box to allow the control access without any restriction. Then, Apply and OK all the tabs to apply all the changes done.

You can now run/execute the query without any errors.

user_148929350

As the message tells you, Postres is not allowed to read the file.

If you want to fix that open the Task Manager, and click on "Show processes from all users". Look for the rows with the image name postgres.exe (likely more than one). Remember the value in the column "User Name" (it's probably NETWORK SERVICE). Open the properties of your file, add that user in the "Security" tab and grant read access to them.

Or use psql's \copy as the message suggests.

user_96614240 
copy us_counties_2010 (your column name)//(country_code, latitude, longitude, country, population)
 FROM 'D:\us_counties_2010 .csv' DELIMITER ',' csv HEADER

Your csv file should be in other than C drive. It can't access your C drive. Store it in D drive or any other it will work perfect.

user_140219300

Change the Location of data file and path to Drive['D:\us_counties_2010.csv' ] it will work.

The permission is denied because your files[us_counties_2010.csv] is in 'C' Drive ['C:\Users\obella\OneDrive\Desktop\us_counties_2010.csv'] which is a Windows Drive and permissions are restricted and may not be changed easily & impossible without administrative privileges.

Good Luck & happy programming!

user_86198800

If you are using PSQL, run it as administrator, then you shouldn't have any problem when you are using COPY

user_180999950

In the case when creating a table as well as importing data from a CSV file, we can skip the query and use the program itself. To do this, simply right-click on your table in the tree on the left and select the Import/Export… menu item.

importing csv file using postgresql

A window will appear with the slider set to Import. Then select the source file and set the format to CSV. Set the Header to Yes if your file has a header. The only thing left is to select the delimiter (usually a comma).

When you click OK, the data will be imported.

For a better understanding, you can refer original article. https://learnsql.com/blog/how-to-import-csv-to-postgresql/

user_166020560 
copy us_counties_2010 from  'C:\Users\obella\OneDrive\Desktop\us_counties_2010.csv' with (FORMAT CSV, HEADER,DELIMITER ',');

Use this code instead of that

user_195012090
Related