Internals

pgJDBC employs a few behaviours that strictly expect a PostgreSQL server on the other end, so that some operations will fail on databases offering wire-compatibility with PostgreSQL, but do not provide certain features like the hstore or jsonb extensions.

The background is that, when using the jdbc:postgresql:// JDBC driver prefix, downstream applications and frameworks will implicitly select the corresponding dialect implementation for PostgreSQL.

Compatibility

While CrateDB is compatible with PostgreSQL on the wire protocol, it needs JDBC support for its SQL dialect, provided by the jdbc:crate:// protocol identifier.

• IDEs like DataGrip and DBeaver need this driver, because they would otherwise emit SQL statements too specific to PostgreSQL.

• The Apache Flink JDBC Connector needs this driver to not select the PostgreSQL dialect, see also Apache Kafka, Apache Flink, and CrateDB.

• Tools like Dataiku need this driver to implement transaction commands like ROLLBACK as a no-op.

Note

For basic and general purpose use, the official PostgreSQL JDBC Driver can also be used. Sometimes, it is easier because pgJDBC is included into many applications out of the box.

Differences to pgJDBC

The driver is based upon a fork of the PostgreSQL JDBC Driver, see pgjdbc driver fork, and adjusts a few details to compensate for behavioral differences of CrateDB. Please take notice of the corresponding implementation notes:

Supported:

• A few metadata functions have been adjusted to better support CrateDB’s type system.

• The CrateDB JDBC driver deserializes objects to a Map, pgJDBC treats them as JSON.

• DDL and DML statements are supported through adjustments to the PgPreparedStatement and PgStatement interfaces.

Unsupported:

• CallableStatement is not supported, as CrateDB itself does not support stored procedures.

• DataSource is not supported.

• ParameterMetaData, e.g. as returned by PreparedStatement, is not supported.

• ResultSet objects are read only (TYPE_FORWARD_ONLY, CONCUR_READ_ONLY), so changes to a ResultSet are not supported.

To learn further details about the compatibility with JDBC and PostgreSQL features, see the specific code changes to the PgConnection, PgDatabaseMetaData, and PgResultSet classes.