Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Correct way to use copy Postgres jdbc

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

like image 523
Mrinal Bhattacharjee Avatar asked Oct 28 '17 10:10

Mrinal Bhattacharjee


People also ask

How to copy input file from stdin to PostgreSQL using JDBC?

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. Thanks for contributing an answer to Stack Overflow!

How to move data into PostgreSQL using PostgreSQL copy command?

When you want to move data into your PostgreSQL database, there are a few options available like pg_dump and Azure Data Factory. The method you pick depends on the scenario you want to enable. Today, we’re exploring three scenarios where you can consider using PostgreSQL’s COPY command.

What is the use of Copyin in PostgreSQL?

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. Show activity on this post.

Can I use \copy on azure database for PostgreSQL?

The server based COPY command has limited file access and user permissions, and isn’t available for use on Azure Database for PostgreSQL. \COPY runs COPY internally, but with expanded permissions and file access. The source file does not have to exist on the same machine as the Postgres instance if you use \COPY.


2 Answers

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.

like image 118
Gord Thompson Avatar answered Oct 11 '22 18:10

Gord Thompson


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();
}
like image 24
a_horse_with_no_name Avatar answered Oct 11 '22 20:10

a_horse_with_no_name