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
sourcefile.
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
COPYdoes.
I have not worked with it however
Thomas
