Re: COPY using Hibernate

Vaibhav Patil wrote:

> The other way I am trying is the pg/plsql stored procedure. As per the
> requirement, I have to insert around a million integers in a table
> having just one column. I am not aware of how to use "COPY FROM STDIN"
> using pg/plsql. I'll pass these integers as array to stored procedure. I
> want to avoid dependency of file, otherwise it could have been done
> easily using "COPY FROM file". Looking for more information on internet
> to solve the issue. If anybody aware of the way, please help me.

It's a real pity the JDBC driver doesn't support the COPY protocol so
you could just do this via PGConnection. There have been patches around
for ages ... though I presume there are good reasons why they haven't
been merged.

Do check to make sure that the stored proc approach is actually faster
than just doing batched multi-valued INSERTs. Open a transaction, then
prepare a statement like this:

INSERT INTO sometable VALUES (?),(?),(?),(?),(?);

(but with say 20 parameters - you'll need to play around and see how
many is optimal), then add a series of invocations of the prepared
statement to a JDBC batch insert/update and execute it.

I wouldn't be surprised if that was quite a bit faster than doing it
with an array and PL/PgSQL.

--
Craig Ringer