sqlite_fdw

Overview

break on el8 due to sqlite-lib version low

Version

Build

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

pig build pkg sqlite_fdw         # build RPM / DEB packages

Install

You can install sqlite_fdw directly. First, make sure the PGDG repository is added and enabled:

pig repo add pgdg -u          # Add PGDG repo and update cache

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

• Install
• pig
• dnf
• apt

pig install sqlite_fdw;          # Install for current active PG version

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

dnf install -y sqlite_fdw_17       # PG 17
dnf install -y sqlite_fdw_16       # PG 16
dnf install -y sqlite_fdw_15       # PG 15
dnf install -y sqlite_fdw_14       # PG 14

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

Create Extension:

CREATE EXTENSION sqlite_fdw;

Usage

sqlite_fdw: SQLite Foreign Data Wrapper

Create Server

CREATE EXTENSION sqlite_fdw;

CREATE SERVER sqlite_server FOREIGN DATA WRAPPER sqlite_fdw
  OPTIONS (database '/path/to/database.db');

Server Options: database (required, path to SQLite file), updatable (default true), truncatable (default false), keep_connections (default true), batch_size (default 1), force_readonly (default false).

No CREATE USER MAPPING is required since SQLite has no authentication model.

Create Foreign Table

CREATE FOREIGN TABLE remote_data (
  id integer OPTIONS (key 'true'),
  name text,
  created timestamp OPTIONS (column_type 'INT'),
  data bytea
)
SERVER sqlite_server
OPTIONS (table 'data_table');

Table Options: table (SQLite table name if different from PostgreSQL name), updatable, truncatable, batch_size.

Column Options: column_name (map to different SQLite column name), column_type (SQLite affinity: INT for epoch timestamps, BLOB for UUIDs), key (mark as primary key for UPDATE/DELETE).

CRUD Operations

SELECT * FROM remote_data WHERE id > 100;
INSERT INTO remote_data (id, name) VALUES (1, 'test');
UPDATE remote_data SET name = 'updated' WHERE id = 1;
DELETE FROM remote_data WHERE id = 1;

Import Foreign Schema

IMPORT FOREIGN SCHEMA public
  FROM SERVER sqlite_server INTO local_schema;

Import Options: import_default (default false), import_not_null (default true).

Data Type Mapping

Timestamps can be stored as TEXT (ISO format) or INT (Unix epoch, use column_type 'INT'). UUIDs can be stored as TEXT (36 chars) or BLOB (16 bytes). The SQLite database file must be readable (and writable for DML) by the PostgreSQL OS user.