0
lease=# COPY dhcpd_data (ip_address, start_time, end_time, mac_address, machine_name) FROM '~/outputcsvre.csv' DELIMITER ',' CSV HEADER;
ERROR:  could not open file "~/outputcsvre.csv" for reading: No such file or directory

if i define the path as '/home/rihiraj12/outputcsvre.csv', it works fine.

melpomene
  • 84,125
  • 8
  • 85
  • 148
Rishiraj Salam
  • 13
  • 1
  • 7

2 Answers2

1

Yes, that's normal.

You don't really have a directory called ~. When you execute a command on the command line, the shell will expand ~ to /home/rihiraj12 before running the program. But here you're not using the shell, so ~ is interpreted literally.

As a workaround you could say

COPY dhcpd_data (...) FROM PROGRAM 'cat ~/outputcsvre.csv' ...

But note that the COPY command is executed by the server, so this will make the server spawn a cat command and use the home directory of the PostgreSQL server.

To specify the file from your own point of view, you can (in psql) use the \copy meta-command (which has the same syntax as COPY):

\copy dhcpd_data (...) FROM PROGRAM 'cat ~/outputcsvre.csv' ...

This will use your own home directory as ~.

melpomene
  • 84,125
  • 8
  • 85
  • 148
1

~ is a shortcut that unix-like shells can expand to be the home directory of your user. i.e. if you use ~/outputcsvre.csv , the shell converts this to /home/rihiraj12/outputcsvre.csv before doing anything else with it.

Outside a shell, applications rarely implement this expansion - and neither does postgresql, so you have to provide real path to the file.

In the case of the COPY command in postgresql, it is executed by the server - so in this case you will have to provide a filename that the server can resolve and read directly. (i.e. a relative path would be relative to wherever the postgresql server is located - so use an absolute path for the file.)

nos
  • 223,662
  • 58
  • 417
  • 506
  • is there any other way to define the path rather than defining it as an actual path? – Rishiraj Salam Oct 19 '18 at 07:31
  • You either need to provide an absolute path such as `/home/rihiraj12/outputcsvre.csv` or a relative path such as `outputcsvre.csv` or `./outputcsvre.csv` or `../outputcsvre.csv` . Relative means a path relative to the current directory you are in. – nos Oct 19 '18 at 07:34
  • @nos For the `COPY` command it's a path relative to the current working directory of the PostgreSQL server, which is probably the cluster's data directory. – melpomene Oct 19 '18 at 07:47
  • Albeit I think the COPY postgresql command is executed by the server. So a relative path there would be relative to wherever the postgresql server runs. – nos Oct 19 '18 at 07:48