4

I use pgAdmin4 and postgresql 10 on a window 10 laptop. When I use a COPY FROM to import a CSV file

COPY BD.CP FROM 'C:\Users\NOMUSER\Desktop\MONDOSSIER\Fichier.csv' with delimiter ';';

PGS returns an error message of Permission denied :

ERROR: ERREUR: n'a pas pu ouvrir le fichier « C:\Users\NOMUSER\Desktop\MONDOSSIER\Fichier.csv » pour une lecture : Permission denied

HINT: COPY TO indique au serveur PostgreSQL de lire un fichier. Vous pourriez vouloir utiliser la fonctionnalité \copy de psql pour lire en local.

SQL state: 42501

What is the problem please ? I look at a solution but I do not find! Thanks a lot :)``

Community
  • 1
  • 1
Logique
  • 61
  • 1
  • 3

5 Answers5

2

Use \copy instead of just "copy". 

COLS=STATE,COUNTY,LINK_ID
psql -d tosm -c "\COPY alias ($COLS) FROM 'alias.txt' (FORMAT CSV, HEADER, DELIMITER('|'))";
Stan Sokolov
  • 2,140
  • 1
  • 22
  • 23
1

Ok, so you (and I) need to change the user authorization of the destination folder from where you want to COPY FROM or COPY TO. At the installation of Postgresql, windows does not create a new service named postgresql but uses Network service. So in the properties of your folder, you need to authorize "Network Service" as a user and to give "total control" on the folder. I have done it and it is ok for now!

Logique
  • 61
  • 1
  • 3
1

Had the same problem on PostgresSQL 10, couldn't neither copy to or from a csv.

From what I gathered the server doesn't have permission to access the folder and the easiest solution is to place it somewhere where it can access like "C:\Users\Public..".

EDIT: There is also the possibility of Importing/Exporting data using pgAdmin's object browser by right-clicking on the tables within the database's public schema and clicking Import/Export...

ninjaunicornofdeath
  • 11
  • 3
0

I have exactly the same problem. I read some explanations saying that COPY can't have access to your files (I don't know really why).

Indeed it does not work even as an administrator :/

You should try to run the command with \copy instead of COPY. I didn't succeed yet as I can't find how to run pgsql functions.

RobC
  • 22,977
  • 20
  • 73
  • 80
J.Delannoy
  • 345
  • 5
  • 15
-1

Check if the user have permission on SELECT/INSERT/DELETE on the table, COPY depends on it. https://www.postgresql.org/docs/current/static/sql-grant.html

VASHIA
  • 26
  • 1
  • 7