I am writing a groovy script ( version 1.8.5) which needs to copy data from a file into a table ( Postgres Database). I could use the SQL command COPY, but that requires the script to run as superuser ( which is not an option I can use). The other alternative is to use \COPY, which is a psql command. But psql is a command line tool for PostgresSQL. Is there a way to call \COPY from within the Groovy script?
I tried the following:
What I am trying to do in the above code is log onto to the psql command line and then run the \COPY command, but I get the following error:
Just wondering if it is possible to call \COPY from a script? If not, is there any other way I can bulk copy the data from the file to the table? Its a huge file, and copying row by row will be very inefficient.
The problem you're having is that you're trying to execute a command which waits for input from the user. The way your code is written, it won't interact with the running process. I did some googling and found out that what you are trying to do is rather tricky because of not knowing if the running program (psql) is ready for input or not.
I'm sorry that I don't have a "how to" answer but at least this might get you started in the right direction of what to search for to find a solution.