12

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

Mrinal Bhattacharjee
  • 1,326
  • 4
  • 10
  • 15
  • You don't need the \\ in from of the copy statement. And what's the error you're getting? – fvu Oct 28 '17 at 10:49
  • Does the file `C:\\_0STUFF\\NSE_DATA\\nseoi_27102017.csv` exist on the server? And does it get loaded if you test the statement manually? – fvu Oct 28 '17 at 10:51
  • postgres-# \copy fno_oi FROM 'C:\_0STUFF\NSE_DATA\nseoi_27102017.csv' DELIMITER ',' CSV header COPY 212 – Mrinal Bhattacharjee Oct 28 '17 at 10:52
  • the same command works from sql shell. – Mrinal Bhattacharjee Oct 28 '17 at 10:55
  • 5
    `\copy` is a `psql` command **not** a [SQL command](https://www.postgresql.org/docs/current/static/sql-commands.html) you need to use `copy` instead: https://www.postgresql.org/docs/current/static/sql-copy.html or use the CopyManager API: https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/copy/CopyManager.html –  Oct 28 '17 at 10:57
  • Thanks for your suggestion. String sql = "copy fno_oi FROM 'C:/_0STUFF/NSE_DATA/nseoi_27102017.csv' DELIMITER ',' CSV header"; try { CopyManager copyManager = new CopyManager((BaseConnection)connection); copyManager.copyIn(sql); -------------------------------------------------------------------------- the above command works but throws an exception.Seems like problem with jdbc driver https://www.postgresql.org/message-id/D960CB61B694CF459DCFB4B0128514C20393819B@exadv11.host.magwien.gv.at org.postgresql.util.PSQLException: Received CommandComplete – Mrinal Bhattacharjee Oct 28 '17 at 11:21

2 Answers2

20

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.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
5

As your input file is stored locally on the computer running your Java program you need to use the equivalent of copy ... from stdin in JDBC because copy can only access files on the server (where Postgres is running).

To do that use the CopyManager API provided by the JDBC driver.

Something along the lines:

Connection connection = DBUtil.getConnection("POSTGRES");

String fileName = "C:/_0STUFF/NSE_DATA/nseoi_" + date + ".csv";
String sql = "copy fno_oi FROM stdin DELIMITER ',' CSV header";

BaseConnection pgcon = (BaseConnection)conection;
CopyManager mgr = new CopyManager(pgcon);

try {

  Reader in = new BufferedReader(new FileReader(new File(fileName)));
  long rowsaffected  = mgr.copyIn(sql, in);

  System.out.println("Rows copied: " + rowsaffected);

} catch (SQLException e) {
  e.printStackTrace();
}