6

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.

Tommy Gibbons
  • 184
  • 1
  • 2
  • 12
  • Had the same issue. File and permissions were correct. However, I was using a proxy to connect to the target server. Make sure the file exists on the target server where you trying to execute this. – viggy28 Feb 08 '21 at 18:19

2 Answers2

10

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.

John Powell
  • 12,253
  • 6
  • 59
  • 67
0

Right click on the file, you want to extract data from. click on permission and in the security tab tick the read and write option.

PRERIT RAJPAL
  • 1