I am new to postgres, I have a table 'file_name' and saved the file name(just one row) 'file' like: '/users/../aaa.csv', how can I use shell script to read the file name then use \copy to load the file aaa.csv?
I have tried use variable in bash shell script psql on mac os 10.9.5 terminal:
\Set filename \\select file from file_name , sometime it works and filename = '/users/../aaa.csv', and sometimes it doesn't work. Even I type in
\set filename '/users/../aaa.csv', to set
:filename = '/users/../aaa.csv', the \copy command didn't work with the variable:
\copy table from :filename
\copy table from :'filename
\copy table from :"filename"
\copy table from \\select file from file_name,
they all failed for No such file or directory, only use
\copy table from 'users/../aaa.csv' worked. Any one can help? thanks.
It looks that \copy doesn't work with internal variables.
You can read the filename in bash interactively (outside psql) and call psql with the complete \copy command like following bash commands:
echo -n "filename to copy into table:" ; read CSVFILE
psql -c "\\copy tablename from '$CSVFILE'"
# Please do not forget psql options such as "-d database" or "-U username" or what else if required.
# Please do not forget to replace "tablename" by your own tablename.