Re: Problem with COPY using PostgreSQL 9 and JDBC 4

On Thu, Nov 25, 2010 at 8:00 AM, Martin Goodfellow <
Martin(dot)Goodfellow(at)cis(dot)strath(dot)ac(dot)uk> wrote:

> Hi all,
> I'm having some trouble using this command via JDBC. I have a
> set of files with which I would like to populate database tables. If I
> use the command, copy table from file with Delimiter ' ';, I get nothing
> being added to the table should it be via JDBC but if I do it on the
> command line I get all the results added as required.
>

Are you actually trying to get postgres to read the data from a file on the
db server's filesystem or are you trying to pass an inputstream/reader to
the jdbc driver's CopyManager functionality in order to pass the data from
the client to the db server? If the latter, you must use 'copy table from
stdin' instead of 'copy table from file.' My code looks something like
this:

StringBuilder sql = new StringBuilder();

sql.append("COPY ");

if (schema != null) {

sql.append(schema).append(".").append(tableName);

} else {

sql.append(tableName);

}

sql.append(" (").append(fields).append(")")

.append(" FROM stdin WITH CSV QUOTE AS ")

.append(StringUtils.isBlank(quote) ? "''''" : quote);

return ((PGConnection) con).getCopyAPI().copyIn(sql.toString(), this.fis);