how to copy data from file to PostgreSQL using JDBC?

Question

I want to copy data from file to PostgreSQL DB using JDBC. I was using JDBC statement object to copy the file into DB. It is very slow.

I came to know that we can also use copy out command to copy file to DB. But, how can I do that with JDBC. Even good reference material having an example of copy in JDBC would help.

PS: thanks in advance

Show us the code, and we might explain why it's slow. In particular, are you using batch updates? Are you using prepared statements? — JB Nizet, Aug 05 '11 at 15:48

score 33 · Accepted Answer · answered Aug 05 '11 at 17:48

This works...

import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;

import org.postgresql.copy.CopyManager;
import org.postgresql.core.BaseConnection;

public class PgSqlJdbcCopyStreamsExample {

    public static void main(String[] args) throws Exception {

        if(args.length!=4) {
            System.out.println("Please specify database URL, user, password and file on the command line.");
            System.out.println("Like this: jdbc:postgresql://localhost:5432/test test password file");
        } else {

            System.err.println("Loading driver");
            Class.forName("org.postgresql.Driver");

            System.err.println("Connecting to " + args[0]);
            Connection con = DriverManager.getConnection(args[0],args[1],args[2]);

            System.err.println("Copying text data rows from stdin");

            CopyManager copyManager = new CopyManager((BaseConnection) con);

            FileReader fileReader = new FileReader(args[3]);
            copyManager.copyIn("COPY t FROM STDIN", fileReader );

            System.err.println("Done.");
        }
    }
}

[pedal-dialect](https://github.com/eclecticlogic/pedal-dialect) allows you to use the copy command directly with JPA entities. — Καrτhικ, Jan 06 '15 at 14:29
The cast `(BaseConnection) con` might not work; in certain cases the connection is wrapped in some other connection type (e.g. when using connection pools or spies). What worked for me was to use `con.unwrap(BaseConnection.class)` instead. — avivr, Feb 12 '18 at 15:13
Thank you! Seems this is the only way to do this via the JDBC — Frankie Drake, May 24 '18 at 15:15

score 2 · Answer 2 · edited Jan 13 '20 at 11:44

(based on aliasmrchips' answer:) if you have a Groovy environment (like me using it within ANT) you could do it like this (substituting the Oracle specifics with Postgres):

// exec.groovy
this.class.classLoader.rootLoader.addURL('${postgres-jdbc-driver-path}')
PgScript.load()

// PgScript.groovy
// (we cannot use the org.postgres.* classes in exec.groovy already!)
import java.io.FileReader
import java.sql.DriverManager
import org.postgresql.copy.CopyManager
import org.postgresql.core.BaseConnection

class PgScript {
    static void load() {        

        DriverManager.getConnection (
            '${jdbc-db-url}', '${db-usr}', '${db-usr-pass}'
        ).withCloseable {conn ->
            new CopyManager((BaseConnection) conn).
                copyIn('COPY t FROM STDIN', new FileReader('${sqlfile}'))
        }
    }
}

Based also on this javaworld.com article.

how to copy data from file to PostgreSQL using JDBC?

2 Answers2

Linked