firebird_fdw

Overview

Version

Build

You can build the RPM / DEB packages for firebird_fdw using pig build:

pig build pkg firebird_fdw         # build RPM / DEB packages

Install

You can install firebird_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 firebird_fdw;          # Install for current active PG version

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

dnf install -y firebird_fdw_18       # PG 18
dnf install -y firebird_fdw_17       # PG 17
dnf install -y firebird_fdw_16       # PG 16
dnf install -y firebird_fdw_15       # PG 15
dnf install -y firebird_fdw_14       # PG 14

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

Create Extension:

CREATE EXTENSION firebird_fdw;

Usage

Sources: upstream README, 1.4.2 README, 1.4.2 tag.

firebird_fdw connects PostgreSQL to Firebird databases through the foreign data wrapper API. It supports reads, writes, IMPORT FOREIGN SCHEMA, predicate pushdown for supported expressions, connection caching, and foreign-table TRUNCATE on PostgreSQL 14+.

Create Server

CREATE EXTENSION firebird_fdw;

CREATE SERVER firebird_server FOREIGN DATA WRAPPER firebird_fdw
  OPTIONS (address 'localhost', database '/path/to/database.fdb');

Server options include:

• address, default localhost.
• port, default 3050.
• database, the Firebird database name or path.
• updatable, default true; table-level settings can override it.
• disable_pushdowns, useful for debugging or benchmarking.
• quote_identifiers, to quote table and column identifiers by default.
• implicit_bool_type, for integer-backed Firebird boolean columns.
• batch_size, for multi-row inserts on PostgreSQL 14+.

Create User Mapping

CREATE USER MAPPING FOR CURRENT_USER SERVER firebird_server
  OPTIONS (username 'sysdba', password 'masterke');

Create Foreign Table

CREATE FOREIGN TABLE fb_test (
  id smallint,
  val varchar(2048)
)
SERVER firebird_server
OPTIONS (table_name 'fdw_test');

With column name mapping:

CREATE FOREIGN TABLE fb_mapped (
  id smallint OPTIONS (column_name 'test_id'),
  val varchar(2048) OPTIONS (column_name 'test_val')
)
SERVER firebird_server
OPTIONS (table_name 'fdw_test');

With a custom query, the foreign table is read-only:

CREATE FOREIGN TABLE fb_query (
  id smallint,
  val varchar(2048)
)
SERVER firebird_server
OPTIONS (query $$ SELECT id, val FROM fdw_test WHERE id > 10 $$);

Table options include table_name, query, updatable, estimated_row_count, quote_identifier, and batch_size. Column options include column_name, quote_identifier, and implicit_bool_type.

Import Foreign Schema

IMPORT FOREIGN SCHEMA someschema
  FROM SERVER firebird_server
  INTO public
  OPTIONS (import_views 'true', verbose 'true');

Import options include import_not_null, import_views, updatable, and verbose. The schema name has no special Firebird meaning and can be any value accepted by PostgreSQL’s IMPORT FOREIGN SCHEMA syntax.

CRUD Operations

SELECT * FROM fb_test WHERE id > 5;
INSERT INTO fb_test VALUES (10, 'new record');
UPDATE fb_test SET val = 'updated' WHERE id = 10;
DELETE FROM fb_test WHERE id = 10;
TRUNCATE fb_test;

UPDATE and DELETE use Firebird’s RDB$DB_KEY. TRUNCATE is implemented as an unqualified Firebird DELETE because Firebird has no native TRUNCATE statement.

Utility Functions

• firebird_fdw_version() returns the FDW version as an integer.
• firebird_fdw_close_connections() closes cached Firebird connections for the current PostgreSQL session.
• firebird_fdw_server_options(servername text) shows effective server option values and whether each was explicitly provided.
• firebird_fdw_diag() returns diagnostic key/value data, including FDW and libfq versions.
• firebird_version() reports Firebird server versions for configured foreign servers and may open connections to do so.

Caveats

• Pigsty packages firebird_fdw 1.4.2 for PostgreSQL 14-18. Upstream documents compatibility with PostgreSQL 10-19, with newer FDW API features available only on newer PostgreSQL releases.
• Upstream supports Firebird 2.5 and later; older Firebird versions are not a tested target.
• firebird_fdw and libfq are developed together, so package compatibility depends on matching those libraries.
• Custom-query foreign tables cannot be updated.
• The implicit_bool_type feature is experimental and is designed around integer columns representing boolean values.