psql ERROR: could not open file "address.csv" for reading: No such file or directory

Question

A newbie to Linux.

I am trying to copy a .csv into a PostgreSQL database,

copy address from 'address.csv' delimiter ',' csv header;

I have cd to location of file, using the \cd [directory] command at the psql prompt. An unsuccessful action.

I have closed that terminal window and in another terminal window cd to the data folder and from there there I have opened the psql command.

\! pwd displays the name/path of folder where the file is stored. /home/tommy/virtualenv_folder/code_data/postgresql_csv_files

\! ls displays the file name, even using the wildcard \! add* displays the file name.

stackoverflow.com/questions/16618299/postgres-copy-from-csv-file-no-such-file-or-directory suggests to reset the search_path. Being in the data folder surely this is not necessary. Or is it?

Anyway, any pointers would be appreciated, please.

Answer 1

You should use the actual path in the copy statement, eg,

copy address from '/home/tommy/virtualenv_folder/code_data/postgresql_csv_files/address.csv'. 

Also make sure that the postgres user has read access to that file and directory or change the ownership to postgres, ie, chown postgres:postgres address.csv. I tend to create a directory and give all users read/write access to it, so I can easily load data into postgres and dump it back out again, either as myself or as a postgres user, eg,

chmod a+rw /var/import/postgresfiles

The search_path relates to postgres searching through schemas within the database not in the external filesystem.