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

You don't need the \\ in from of the copy statement. And what's the error you're getting? — fvu
– fvu, Commented Oct 28, 2017 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
– fvu, Commented Oct 28, 2017 at 10:51
postgres-# \copy fno_oi FROM 'C:_0STUFF\NSE_DATA\nseoi_27102017.csv' DELIMITER ',' CSV header COPY 212 — Mrinal Bhattacharjee
– Mrinal Bhattacharjee, Commented Oct 28, 2017 at 10:52
\copy is a psql command not a SQL command you need to use copy instead: postgresql.org/docs/current/static/sql-copy.html or use the CopyManager API: jdbc.postgresql.org/documentation/publicapi/org/postgresql/copy/… — user330315
– user330315, Commented Oct 28, 2017 at 10:57

Gord Thompson · Accepted Answer · 2017-10-28 15:24:40Z

26

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.

answered Oct 28, 2017 at 15:24

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ILYAS_Kerbal Over a year ago

Thanks a lot! You saved the day Gord, the CopyManager is what I was looking to work with STDIN

Marco Carlo Moriggi Feb 19 at 15:02

Remember to close the Reader you created at 5th line or we'll have a bunch of open files in copypasted pieces of software ;-) (since it's from 2017 I think that even ChatGPT should've learned to let them open)

user330315 · Accepted Answer · 2017-10-28 15:23:38Z

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();
}

answered Oct 28, 2017 at 15:23

user330315

2 Comments

Mrinal Bhattacharjee Over a year ago

what's the recommended way if postgress server and application server are different

OrangeDog Over a year ago

@MrinalBhattacharjee CopyManager

Collectives™ on Stack Overflow

Correct way to use copy Postgres jdbc

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related