COPY for PostgreSQL 8.x JDBC Driver

Copy is an efficient (typically tens of times faster than INSERT) bulk data exchange method for PostgreSQL databases. This is a driver extension to support COPY FROM STDIN and COPY TO STDOUT data exchange with the client for series 8 JDBC drivers. Derived from earlier implementation for series 7, it provides a simple Stream based API while keeping low level protocol handling in the internal class meant for that purpose.

Roadmap

• Encoder and decoder to hide conversion to server character set
• Serializer and deserializer to present the data as objects rather than byte stream
• Binary copy format support with above serializers
• Caching (row based, to work around server glitches with abrupt packet ending)

History

• 2007-08-02 Published an alternative, locking version as proposed by Neil Conway
• 2007-07-19 Fixed CommandComplete bug reported by Thomas Finneid
• 2007-07-18 Fixed notification handling as suggested by Oliver Jowett.
• 2007-07-16 Fixed mistakes pointed out by Kris Jurka
• 2007-06-27 Published at http://kato.iki.fi/db/postgresql/jdbc/copy with cosmetic touches
• 2007-06-21 First stream-based version published on pgsql-jdbc mailing list

Sources and Binaries

I'm trying to get Copy support accepted into the official driver. Meanwhile and towards that end patches are provided here. Below are links to separate patches for driver versions 8.1 - 8.3; differences between them are minor (reflecting changes in debugging and integer receiving in v3/QueryExecutorImpl.java).

Full original source without the patches is available at the official site: http://jdbc.postgresql.org/download.html#jdbcselection

Readily usable jar files compiled with Java 5 for protocol 3 and JDBC3 are also available.

• postgresql-jdbc-8.3dev-600-copy-20070719.diff.gz (5491 bytes); postgresql-jdbc-8.3dev-600-copy-20070719.jdbc3.jar (431037 bytes)
• postgresql-jdbc-8.1-409-copy-20070719.diff.gz (5504 bytes); postgresql-jdbc-8.1-409-copy-20070719.jdbc3.jar (407825 bytes)
• postgresql-jdbc-8.2-505-lockcopy-20070802.diff.gz (10803 bytes); postgresql-jdbc-8.2-505-lockcopy-20070802.jdbc3.jar (435662 bytes)
• postgresql-jdbc-8.2-505-copy-20070719.diff.gz (5486 bytes); postgresql-jdbc-8.2-505-copy-20070719.jdbc3.jar (430124 bytes)

Lockcopy is an alternative version that locks the connection and provides writable and readable stream-like interfaces.

Patch contents

org/postgresql/PGConnection.java

-- declaration of getCopyAPI()

org/postgresql/copy/CopyManager.java

-- Copy API

org/postgresql/core/QueryExecutor.java

-- declaration of executeCopy(...)

org/postgresql/core/v2/QueryExecutorImpl.java

-- stub of executeCopy(...) that just throws an explaining exception

org/postgresql/core/v3/QueryExecutorImpl.java

-- actual executeCopy(...) that handles data exchange with the server

org/postgresql/jdbc2/AbstractJdbc2Connection.java

-- implementation of getCopyAPI()

org/postgresql/test/jdbc2/CopyTest.java

-- tests to confirm that basic copying in and out of database works

org/postgresql/test/jdbc2/Jdbc2TestSuite.java

-- inclusion of copy tests in JDBC2 test suite

Examples

PgSqlJdbcCopyStreamsExample.java

javac -cp postgresql.jar PgSqlJdbcCopyStreamsExample.java &&
java -cp postgresql.jar:. PgSqlJdbcCopyStreamsExample jdbc:postgresql://localhost:5432/test test password < PgSqlJdbcCopyStreamsExample.java
Copy rows from stdin into temporary table, then contents of that table to stdout.