Postgresql – How to use the `COPY` command with JDBC

copyjdbcpostgresql

So I've been working with postgre databases and I noticed that it throws an error sometimes when I try to read a csv file and copy to a table remotely. It works perfectly when I use a buffered reader but i'm trying to see if there's a way i can get i to work using the databases COPY FROM method. It works perfectly on the computer where the database is located but my other computer gets an error.

Error occurs in this line:

st.execute("COPY inputdata FROM 'C:\\Users\\JAMES\\Downloads\\V24_Mike_110217_RemovedReturnTrips\\RyderCombiner\\AllNonMatchingWithColNames.csv' CSV HEADER DELIMITER ',';");

Here's where I connect to database:

Class.forName("org.postgresql.Driver");
Connection conn = DriverManager.getConnection("jdbc:postgresql://192.168.1.15:5432/postgres", "postgres", "pass");

I read online that maybe STDIN can possibly help me but I haven't been able to get it to work and most examples online are using pqsl. Is there a way I can get tthis to work or do I need to go back to buffered readers?

UPDATE:

So I've tried using the CopyManager API but it has an error when trying to make a connection using DBUtil as it is not recognized even though I downloaded the jar file from this website: http://commons.apache.org/proper/commons-dbutils/download_dbutils.cgi

Code:

    Connection connection = DBUtil.getConnection("POSTGRES");
    String sql = "COPY inputdata FROM 'C:\\Users\\JAMES\\Downloads\\V24_Mike_110217_RemovedReturnTrips\\RyderCombiner\\AllNonMatchingWithColNames.csv' CSV HEADER DELIMITER ','";
    CopyManager copyManager = new CopyManager((BaseConnection)connection); 
    copyManager.copyIn(sql);

I get the error in the first line with DBUtil…is importing the jar file the only way to make jdbc recognize this variable?

UPDATE2:

Thanks again guys I ended up getting the copyin to work by following the given link and a few adjustments.

public static void readInputData(String inputDataFile, Statement st) throws FileNotFoundException, IOException, SQLException {
    Connection connection = DriverManager.getConnection("jdbc:postgresql://192.168.1.15:5432/postgres", "postgres", "pass");
    String sql = "COPY INPUTDATA FROM stdin CSV HEADER DELIMITER ','";
    BaseConnection pgcon = (BaseConnection)connection;
    CopyManager mgr = new CopyManager(pgcon);
    try {
        Reader in = new BufferedReader(new FileReader(new File(inputDataFile)));
        long rowsaffected  = mgr.copyIn(sql, in);
    } catch (SQLException ex) {
        System.err.println(ex.getClass().getName() + ": " + ex.getMessage());
        System.exit(0);
    }
}

Best Answer

It works perfectly when I use a buffered reader but i'm trying to see if there's a way i can get i to work using the databases COPY FROM method. It works perfectly on the computer where the database is located but my other computer gets an error.

No you have to use a BufferedReader, or InputStream

  1. COPY opens a file handle on the server. It just needs a path, because it can open and manage its own file handle, and that's more efficient.
  2. For client-side COPY, you have to use libpq PQputCopyData. Internally, client-side COPY is COPY FROM STDIN. The client has to send the data. This is what psql's \COPY does internally. In Java, the function that calls PQputCopyData is copyIn which requires a BufferedReader, or InputStream.