Re: COPY in Java?

John R Pierce schrieb am 18.07.2016 um 22:34:
> I'm looking at the JDBC documentation at
> https://jdbc.postgresql.org/documentation/head/index.html and not
> seeing any mention of COPY and any semantics for using COPY FROM
> STDIN or COPY TO STDOUT... google has a link to a 404 page with some
> sort of CopyManager

https://jdbc.postgresql.org/documentation/publicapi/index.html

indeed returns a 404

> we may need to develop a specialized sort of sql loader to replace an
> existing Oracle tool, we need to intercept bad rows and put them in
> an exceptions file, while bulk loading the good data. I'm thinking
> we batch N rows into COPY, and if a row faults, put it in the
> exception pool then retry the batch without it. the source data
> needs to be massaged before it can be fed to copy, so we can't use
> COPY FROM filename ...

The CopyManager is actually quite easy to use:

org.postgresql.core.BaseConnection con = (BaseConnection)DriverManager.getConnection(....);
CopyManager mgr = new CopyManager(con);

Reader in = new BuffereReader(new FileReader("..."));
mgr.copyIn("copy target_table from stdin with (...)", in);

But I don't think it's suitable for what you need to do because:

* The input file needs to be formatted so that COPY can handle it.
* there is no way you can catch the bad rows with that.

I assume the first problem could be solved by implementing your own Reader that "massages" each line while it reads the source file.

But I don't think there is an efficient way to "catch" the bad rows through the Copy API.

But you might be interested in http://pgloader.io/ which is a tool similar to SQL*Loader it supports more formats then COPY does.
I have not worked with it however

Thomas