jdbc_fdw

Overview

Package/source version 0.5.0; SQL extension version 1.2. PIGSTY RPM and DEB packages cover PostgreSQL 14-18 on x86_64 and aarch64; PGDG RPM 0.4.0 is a legacy alternative for PostgreSQL 14-16. Live queries require a JDBC driver and remote database.

Version

Build

You can build the DEB packages for jdbc_fdw using pig build:

pig build pkg jdbc_fdw         # build DEB packages

Install

You can install jdbc_fdw directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install jdbc_fdw;          # Install for current active PG version

pig ext install -y jdbc_fdw -v 18  # PG 18
pig ext install -y jdbc_fdw -v 17  # PG 17
pig ext install -y jdbc_fdw -v 16  # PG 16
pig ext install -y jdbc_fdw -v 15  # PG 15
pig ext install -y jdbc_fdw -v 14  # PG 14

dnf install -y jdbc_fdw_18       # PG 18
dnf install -y jdbc_fdw_17       # PG 17
dnf install -y jdbc_fdw_16       # PG 16
dnf install -y jdbc_fdw_15       # PG 15
dnf install -y jdbc_fdw_14       # PG 14

apt install -y postgresql-18-jdbc-fdw   # PG 18
apt install -y postgresql-17-jdbc-fdw   # PG 17
apt install -y postgresql-16-jdbc-fdw   # PG 16
apt install -y postgresql-15-jdbc-fdw   # PG 15
apt install -y postgresql-14-jdbc-fdw   # PG 14

Create Extension:

CREATE EXTENSION jdbc_fdw;

Usage

Sources:

• jdbc_fdw v0.5.0 README
• Extension control file
• jdbc_fdw v0.5.0 release

jdbc_fdw exposes a JDBC data source as PostgreSQL foreign tables and can execute remote SQL through a helper function. Use it when a suitable JDBC driver exists but no more specialized FDW is available; the JVM, driver JAR, credentials, and remote query behavior all run inside a PostgreSQL backend process.

Core Workflow

CREATE EXTENSION jdbc_fdw;

CREATE SERVER reporting_jdbc
  FOREIGN DATA WRAPPER jdbc_fdw
  OPTIONS (
    drivername 'org.postgresql.Driver',
    url 'jdbc:postgresql://db.example/reporting',
    jarfile '/opt/jdbc/postgresql.jar',
    querytimeout '10',
    maxheapsize '256'
  );

CREATE USER MAPPING FOR app_user
  SERVER reporting_jdbc
  OPTIONS (username 'reader', password 'secret');

CREATE FOREIGN TABLE remote_orders (
  id bigint OPTIONS (key 'true'),
  created_at timestamptz,
  total numeric
) SERVER reporting_jdbc;

SELECT * FROM remote_orders WHERE id = 42;

There are no table-level options in v0.5.0. Foreign columns map by name. Mark the remote primary-key column with OPTIONS (key 'true') when UPDATE or DELETE needs row identity.

Import and Direct SQL

IMPORT FOREIGN SCHEMA public
  FROM SERVER reporting_jdbc
  INTO jdbc_import
  OPTIONS (recreate 'true');

SELECT *
FROM jdbc_exec('reporting_jdbc', 'SELECT id, name FROM customer')
  AS t(id bigint, name text);

The upstream README says IMPORT FOREIGN SCHEMA currently works only with GridDB. jdbc_exec returns record, so queries returning columns require a column definition list.

Important Options and Limits

• Server options: required drivername and url, absolute jarfile, plus querytimeout and JVM maxheapsize.
• User-mapping options: username and password.
• Column option: key = true identifies primary-key columns for writable operations.
• jdbc_exec(connname, sql) executes driver-specific SQL and can return a defined record set.

Version 0.5.0 supports predicate, column, and aggregate pushdown according to the upstream project, but not remote RETURNING, GROUP BY, ORDER BY, casts, or transaction-control statements. Arrays and foreign-table TRUNCATE are not implemented. Test type conversion and write semantics with the selected driver.

Protect JAR paths and server definitions from untrusted users, keep passwords in user mappings, and bound the JVM heap and remote query time. The source/package release is 0.5.0 while jdbc_fdw.control continues to declare SQL extension version 1.2; use pg_extension.extversion rather than assuming those version spaces are identical.