Correct way to use copy Postgres jdbc

Question

Unable to use copy command with jdbc Postgres. Whats wrong with the below code snippet sample.

public boolean loadReportToDB(String date) {
        // TODO Auto-generated method stub
        Connection connection = DBUtil.getConnection("POSTGRESS");
        String fileName = "C:/_0STUFF/NSE_DATA/nseoi_" + date + ".csv";
        String sql = "\\copy fno_oi FROM 'C:\\_0STUFF\\NSE_DATA\\nseoi_27102017.csv' DELIMITER ',' CSV header";
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            System.out.println("query"+ps.toString());
            int rowsaffected = ps.executeUpdate();
            System.out.println("INT+" + rowsaffected);
            return true;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return false;
    }

org.postgresql.util.PSQLException: ERROR: syntax error at or near "\"
  Position: 1
    at org.

if we use

String sql = "copy fno_oi FROM 'C:\\_0STUFF\\NSE_DATA\\nseoi_27102017.csv' DELIMITER ',' CSV header";

then no rows are updated

postgres version postgresql-10.0-1-windows-x64

Answer 1

This works for me:

try (Connection conn = DriverManager.getConnection(connUrl, myUid, myPwd)) {
    long rowsInserted = new CopyManager((BaseConnection) conn)
            .copyIn(
                "COPY table1 FROM STDIN (FORMAT csv, HEADER)", 
                new BufferedReader(new FileReader("C:/Users/gord/Desktop/testdata.csv"))
                );
    System.out.printf("%d row(s) inserted%n", rowsInserted);
}

Using copyIn(String sql, Reader from) has the advantage of avoiding issues where the PostgreSQL server process is unable to read the file directly, either because it lacks permissions (like reading files on my Desktop) or because the file is not local to the machine where the PostgreSQL server is running.