Search code examples
postgresqltilde-expansion

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

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.

Solution

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