PostgreSQL JDBC Driver

PostgreSQL JDBC Driver (PgJDBC for short) allows Java programs to connect to a PostgreSQL database using standard, database independent Java code. Is an open source JDBC driver written in Pure Java (Type 4), and communicates in the PostgreSQL native network protocol.

Status

Supported PostgreSQL and Java versions

The current version of the driver should be compatible with PostgreSQL 8.2 and higher using the version 3.0 of the protocol, and Java 6 (JDBC 4.0), Java 7 (JDBC 4.1) and Java 8 (JDBC 4.2). Unless you have unusual requirements (running old applications or JVMs), this is the driver you should be using.

PgJDBC regression tests are run against all PostgreSQL versions since 8.2, including "build PostgreSQL from git master" version. There are other derived forks of PostgreSQL but have not been certified to run with PgJDBC. If you find a bug or regression on supported versions, please fill an Issue.

Get the Driver

Most people do not need to compile PgJDBC. You can download the precompiled driver (jar) from the PostgreSQL JDBC site or using your chosen dependency management tool:

Maven Central

You can search on The Central Repository with GroupId and ArtifactId for:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.1.4</version>
</dependency>

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.1.4.jre7</version>
</dependency>

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.1.4.jre6</version>
</dependency>

Development snapshots

Snapshot builds (builds from master branch) are also deployed to Maven Central, so you can test current development version (test some bugfix) using:

<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <version>42.1.5-SNAPSHOT</version> <!-- Java 8 -->
  <version>42.1.5.jre7-SNAPSHOT</version> <!-- Java 7 -->
  <version>42.1.5.jre6-SNAPSHOT</version> <!-- Java 6 -->
</dependency>

There are also available (snapshot) binary RPMs in Fedora's Copr repository.

Changelog

Notable changes for:

42.1.4 (2017-08-01):

• Statements with non-zero fetchSize no longer require server-side named handle. This might cause issues when using old PostgreSQL versions (pre-8.4)+fetchSize+interleaved ResultSet processing combo. see issue 869

42.1.3 (2017-07-14):

• fixed NPE in PreparedStatement.executeBatch in case of empty batch (regression since 42.1.2) PR#867

42.1.2 (2017-07-12):

• Better logic for returning keyword detection. Previously, pgjdbc could be defeated by column names that contain returning, so pgjdbc failed to "return generated keys" as it considered statement as already having returning keyword PR#824 201daf1d
• Replication API: fix issue #834 setting statusIntervalUpdate causes high CPU load PR#835 59236b74
• perf: use server-prepared statements for batch inserts when prepareThreshold>0. Note: this enables batch to use server-prepared from the first executeBatch() execution (previously it waited for prepareThreshold executeBatch() calls) abc3d9d7

42.1.1 (2017-05-05):

• fix: infinite dates might be corrupted when transferred in binary for certain JREs. For instance, 5881610-07-11 instead of infinity.

42.1.0 (2017-05-04):

• fix: data being truncated in setCharacterStream (the bug introduced in 42.0.0) PR#802
• fix: calculation of lastReceiveLSN for logical replication PR#801
• fix: make sure org.postgresql.Driver is loaded when accessing though DataSource interface #768
• feat: support fetching a REF_CURSOR using getObject PR#809
• note: there's no 42.1.0.jre6 due to infinity handling bug. Fixed in 42.1.1.jre6

42.0.0 (2017-02-20):

• Support for PostgreSQL versions below 8.2 was dropped
• java.util.logging is now used for logging: logging documentation
• Ensure executeBatch() can be used with pgbouncer. Previously pgjdbc could use server-prepared statements for batch execution even with prepareThreshold=0 (see issue 742)
• Replication protocol API was added: replication API documentation, GitHub PR 550
• Version bumped to 42.0.0 to avoid version clash with PostgreSQL version
• Error position is displayed when SQL has unterminated literals, comments, etc (see issue 688)

9.4.1212 (2016-11-02):

• ? can now be used in non-prepared statements (fixed regression of 9.4.1210)

9.4.1211 (2016-09-18):

• json type is returned as PGObject like in pre-9.4.1210 (fixed regression of 9.4.1210)
• 'current transaction is aborted' exception includes the original exception via caused-by chain

9.4.1210 (2016-09-07):

• BUG: json datatype is returned as java.lang.String object, not as PGObject (fixed in 9.4.1211)
• Better support for RETURN_GENERATED_KEYS, statements with RETURNING clause
• Avoid user-visible prepared-statement errors if client uses DEALLOCATE/DISCARD statements (invalidate cache when those statements detected)
• Avoid user-visible prepared-statement errors if client changes search_path (invalidate cache when set search_path detected)
• Support comments when replacing {fn ...} JDBC syntax
• Support for Types.REF_CURSOR

9.4.1209 (2016-07-15):

• Many improvements to insert into .. values(?,?) => insert .. values(?,?), (?,?)... rewriter. Give it a try by using reWriteBatchedInserts=true connection property. 2-3x improvements for insert batch can be expected
• Full test suite passes against PostgreSQL 9.6, and OpenJDK 9
• Performance optimization for timestamps (~TimeZone.getDefault optimization)
• Allow build-from-source on GNU/Linux without maven repositories, and add Fedora Copr test to the regression suite

Read the History of Changes for reference of previous versions.

---

Documentation

For more information you can read the PgJDBC driver documentation or for general JDBC documentation please refer to The Java™ Tutorials.

Driver and DataSource class

Implements	Class
java.sql.Driver	org.postgresql.Driver
javax.sql.DataSource	org.postgresql.ds.PGSimpleDataSource
javax.sql.ConnectionPoolDataSource	org.postgresql.ds.PGConnectionPoolDataSource
javax.sql.XADataSource	org.postgresql.xa.PGXADataSource

Building the Connection URL

The driver recognises JDBC URLs of the form:

jdbc:postgresql:database
jdbc:postgresql:/
jdbc:postgresql://host/database
jdbc:postgresql://host/
jdbc:postgresql://host:port/database
jdbc:postgresql://host:port/

The general format for a JDBC URL for connecting to a PostgreSQL server is as follows, with items in square brackets ([ ]) being optional:

jdbc:postgresql://[host][:port][/[database]][?property1=value1[&property2=value2]...]

where:

• jdbc:postgresql: (Required) is known as the sub-protocol and is constant.
• host (Optional) is the server address to connect. This could be a DNS or IP address, or it could be localhost or 127.0.0.1 for the local computer. To specify an IPv6 address your must enclose the host parameter with square brackets (jdbc:postgresql://[::1]:5740/accounting). Defaults to localhost.
• port (Optional) is the port number listening on the host. Defaults to 5432.
• database (Optional) is the database name. Defaults to the same name as the user name used in the connection.
• propertyX (Optional) is one or more option connection properties. For more information see Connection properties.

Connection Properties

In addition to the standard connection parameters the driver supports a number of additional properties which can be used to specify additional driver behaviour specific to PostgreSQL™. These properties may be specified in either the connection URL or an additional Properties object parameter to DriverManager.getConnection.

Property	Type	Default	Description
user	String	null	The database user on whose behalf the connection is being made.
password	String	null	The database user's password.
ssl	Boolean	false	Control use of SSL (true value causes SSL to be required)
sslfactory	String	null	Provide a SSLSocketFactory class when using SSL.
sslfactoryarg	String	null	Argument forwarded to constructor of SSLSocketFactory class.
sslmode	String	null	Parameter governing the use of SSL.
sslcert	String	null	The location of the client's SSL certificate
sslkey	String	null	The location of the client's PKCS#8 SSL key
sslrootcert	String	null	The location of the root certificate for authenticating the server.
sslhostnameverifier	String	null	The name of a class (for use in Class.forName(String)) that implements javax.net.ssl.HostnameVerifier and can verify the server hostname.
sslpasswordcallback	String	null	The name of a class (for use in Class.forName(String)) that implements javax.security.auth.callback.CallbackHandler and can handle PasswordCallback for the ssl password.
sslpassword	String	null	The password for the client's ssl key (ignored if sslpasswordcallback is set)
sendBufferSize	Integer	-1	Socket write buffer size
recvBufferSize	Integer	-1	Socket read buffer size
loggerLevel	String	null	Logger level of the driver using java.util.logging. Allowed values: OFF, DEBUG or TRACE.
loggerFile	String	null	File name output of the Logger, if set, the Logger will use a FileHandler to write to a specified file. If the parameter is not set or the file can't be created the ConsoleHandler will be used instead.
allowEncodingChanges	Boolean	false	Allow for changes in client_encoding
logUnclosedConnections	Boolean	false	When connections that are not explicitly closed are garbage collected, log the stacktrace from the opening of the connection to trace the leak source
binaryTransferEnable	String	""	Comma separated list of types to enable binary transfer. Either OID numbers or names
binaryTransferDisable	String	""	Comma separated list of types to disable binary transfer. Either OID numbers or names. Overrides values in the driver default set and values set with binaryTransferEnable.
prepareThreshold	Integer	5	Statement prepare threshold. A value of -1 stands for forceBinary
preparedStatementCacheQueries	Integer	256	Specifies the maximum number of entries in per-connection cache of prepared statements. A value of 0 disables the cache.
preparedStatementCacheSizeMiB	Integer	5	Specifies the maximum size (in megabytes) of a per-connection prepared statement cache. A value of 0 disables the cache.
defaultRowFetchSize	Integer	0	Positive number of rows that should be fetched from the database when more rows are needed for ResultSet by each fetch iteration
loginTimeout	Integer	0	Specify how long to wait for establishment of a database connection.
connectTimeout	Integer	10	The timeout value used for socket connect operations.
socketTimeout	Integer	0	The timeout value used for socket read operations.
tcpKeepAlive	Boolean	false	Enable or disable TCP keep-alive.
ApplicationName	String	null	The application name (require server version >= 9.0)
readOnly	Boolean	true	Puts this connection in read-only mode
disableColumnSanitiser	Boolean	false	Enable optimization that disables column name sanitiser
assumeMinServerVersion	String	null	Assume the server is at least that version
currentSchema	String	null	Specify the schema to be set in the search-path
targetServerType	String	any	Specifies what kind of server to connect, possible values: any, master, slave, preferSlave
hostRecheckSeconds	Integer	10	Specifies period (seconds) after host statuses are checked again in case they have changed
loadBalanceHosts	Boolean	false	If disabled hosts are connected in the given order. If enabled hosts are chosen randomly from the set of suitable candidates
socketFactory	String	null	Specify a socket factory for socket creation
socketFactoryArg	String	null	Argument forwarded to constructor of SocketFactory class.
autosave	String	never	Specifies what the driver should do if a query fails, possible values: always, never, conservative
preferQueryMode	String	extended	Specifies which mode is used to execute queries to database, possible values: extended, extendedForPrepared, extendedCacheEverything, simple
reWriteBatchedInserts	Boolean	false	Enable optimization to rewrite and collapse compatible INSERT statements that are batched.

Contributing

For information on how to contribute to the project see the Contributing Guidelines

---

Sponsors

• PostgreSQL International
• YourKit