CODEWITHSUNDEEP
groovypostgresql
rjane
rjane

Reputation: 31

Calling a psql command from a Groovy script

I am writing a groovy script 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:

// groovy method to populate table
populateTable( tableName, filePath ) {

    def command = """psql -U<database name>"""
    def proc = command.execute()                
    proc.waitFor()                                

    command = """ \\COPY $tableName FROM '$filePath' WITH DELIMITER AS ','"""
    proc = command.execute()                
    proc.waitFor()  
}

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:

java.io.IOException: Cannot run program "\COPY": java.io.IOException: error=20, Not a directory

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.

Thanks so much in advance for all your help.

Upvotes: 3

Views: 2710

Answers (1)

Phuong LeCong
Phuong LeCong

Reputation: 1854

Your code is currently executing two separate things psql and \COPY (which isn't found given the error message you entered). I'm not as familiar Postgres as MySQL, but looking at the psql documentation it looks like you can pass in the command to run using -c parameter:

def populateTable( tableName, filePath ) {
    def sql = """ \\COPY $tableName FROM '$filePath' WITH DELIMITER AS ',' """
    def command = """psql -U<database name> -c "$sql" """
    def proc = command.execute()
    proc.waitFor()
}

Upvotes: 1

Related Questions