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