2

I'm trying to import a CSV file into postgresql database.
First of all I tried following query:

copy temporaryData
from '/home/milad/Desktop/myFolder/csvFiles/test.csv'
delimiter ',' csv;

But it gave me below Error:

SQL Error [42501]: ERROR: could not open file "/home/milad/Desktop/myFolder/test.csv" for reading: Permission denied

This error is reasonable as suggested by this thread and also by postgresql tutorial:

Notice that the file must be read directly by the PostgreSQL server, not by the client application. Therefore, it must be accessible by the PostgreSQL server machine. Also, you need to have superuser access in order to execute the COPY statement successfully.

Since I don't like to change my system permissions just to import a file, To solve this issue I decided to move my CSV file to /tmp/ directory which is suggested by this answer.

Then I tried to run below script:

create or replace procedure loadInstructorOfCourses()
language plpgsql
as $$
begin 
    create table temporaryData(
        courseSectionID varchar(10),
        instructorID    varchar(25),
        courseTitle     varchar(50)
    );

    copy temporaryData
    from '/tmp/test.csv'
    delimiter ',' csv;

    drop table temporaryData;
end;$$;

call loadInstructorOfCourses();

And now it gives me below error:

SQL Error [58P01]: ERROR: could not open file "/tmp/test.csv" for reading: No such file or directory

But I'm sure the file has read permission for others and also it's available:

> ls -l -d /tmp
drwxrwxrwt 25 root root 700 Oct 20 09:53 /tmp
> ls -l /tmp/test.csv
-rw-r--r-- 1 milad milad 6700 Oct 20 09:53 /tmp/test.csv

Why does this problem happen and how to resolve it?

INFO: My OS is Manjaro KDE:

> uname -r
5.4.150-1-MANJARO

And also I use DBeaver to connect to postgresql server and run my scripts.

UPDATES: Using psql I have the same issue too. When I connect to db by psql:

psql -U postgres -d IUTPlanning

and run my procedure:

call loadInstructorOfCourses();

I'll get the same error:

ERROR:  could not open file "/tmp/test.csv" for reading: No such file or directory
HINT:  COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's \copy.
CONTEXT:  SQL statement "copy temporaryData
        from '/tmp/test.csv'
        delimiter ',' csv"
PL/pgSQL function loadinstructorofcourses() line 9 at SQL statement
milad
  • 1,854
  • 2
  • 11
  • 27
  • What user are you running the query as? COPY targeting a file is only allowed if you are running the query as a DB superuser, regardless of filesystem permissions – cogitoergosum Oct 20 '21 at 08:24
  • @cogitoergosum How can I find what user I am running the query as? I think it's the `postgres` because even running the script using psql has the same issue too: `psql -U postgres -d mydb` – milad Oct 20 '21 at 08:32

1 Answers1

0

Try using psql's \copy command, as the hint in the error message you quoted suggests. COPY FROM tells the server to open the file and process it, while \copy reads the file client side and then passes the output to the server, see https://stackoverflow.com/a/19466558/12760895

Open the psql console with psql -U postgres -d [yourdb] then run

\copy [target_table] from '/tmp/test.csv' DELIMITER ',' CSV;
cogitoergosum
  • 591
  • 4
  • 10
  • I know I can do it by `\copy` but our client couldn't access to neither file system nor executing other programs such as psql, We need to do it in server side by calling procedures and not even by executing `COPY` command directly. Anyway thanks for your time and posting an answer – milad Oct 20 '21 at 09:23
  • If you need to do that server side, looks like the problem is that postgres system user has trouble accessing the file system for some reason. Just a wild guess, is postgres running in a chrooted environment, perhaps? That may explain its inability to read a file in /tmp/. You could try logging in on the server as the postgres user and then checking if you can read the file via the command line. – cogitoergosum Oct 20 '21 at 09:34