No such file or directory when we define the path as '~/path/to/csv' in postgres

Question

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.

Answer 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 ~.

Answer 2

~ 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.)