Experiences with PostgreSQL Java/JDBC Copy API for bulk inserts

Question

With version 8.4 PostgreSQL finally integrated a proprietary API into their JDBC driver, which allows stream based inserts and selects. The so called Copy API grants access to COPY TO/COPY FROM SQL commands, which read text data from a stream/reader into one table at a time or write text data to a stream/writer from one table. Constraints and triggers are regarded for insert operations. Basic transformations (delimiter, quotation, null values etc.) are available. The performance gain is quite impressive, which probably is because of less object instantiation and a much simpler protocol between client and server backend.

Has anyone experiences with this API, good or bad. Is it production ready? Are there any pitfalls one has to be aware of? BTW: The fact that it is a proprietary API is a non-issue for me.

filiprem · Accepted Answer · 2011-01-18 14:07:27Z

1

The COPY API is present in PostgreSQL C library for at least 6 years. It is very stable.

See: http://www.postgresql.org/docs/9.0/interactive/libpq-copy.html and http://www.postgresql.org/docs/9.0/interactive/sql-copy.html

JDBC implementation should have same properties, but I haven't used it.

PS. I think there is a misunderstanding when you call this "proprietary". Both protocol specification and server/client/driver source code is free (as in freedom).

answered Jan 18, 2011 at 14:07

filiprem

7,2131 gold badge35 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Horst Dehmer Over a year ago

With 'proprietary' I meant that it's not part of the JDBC specification and therefore not applicable with products of other DBMS vendors.

Collectives™ on Stack Overflow

Experiences with PostgreSQL Java/JDBC Copy API for bulk inserts

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related