SQLite Foreign Data Wrapper for PostgreSQL

This is a foreign data wrapper (FDW) to connect PostgreSQL to SQLite database file. This FDW works with PostgreSQL 13, 14, 15, 16, 17 and confirmed with SQLite 3.49.0.

+

Also this foreign data wrapper (FDW) can connect PostgreSQL with PostGIS to SpatiaLite SQLite database file. This FDW works with PostGIS 2+ and confirmed with SpatiaLite 5.1. See GIS support description.

+

Contents

1. Features
2. Supported platforms
3. Installation
4. Usage
5. Functions
6. Identifier case handling
7. Generated columns
8. Character set handling
9. Examples
10. Limitations
11. Tests
12. Contributing
13. Useful links

Features

Common features

• Transactions
• Support INSERT/UPDATE/DELETE (both Direct modification and Foreign modification), see access control about conditions of succesfully data modification.
• Support attached SQLite databases as additional schemas of a FOREIGN SERVER.
• Support TRUNCATE by deparsing into DELETE statement without WHERE clause.
• Allow control over whether foreign servers keep connections open after transaction completion. This is controlled by keep_connections and defaults to on.
• Support list cached connections to foreign servers by using function sqlite_fdw_get_connections()
• Support discard cached connections to foreign servers by using function sqlite_fdw_disconnect(), sqlite_fdw_disconnect_all().
• Support Bulk INSERT by using batch_size option
• Support INSERT/UPDATE with generated column
• Support INSERT ... ON CONFLICT DO NOTHING
• Support WITH CHECK OPTION views after a foreign table
• Support mixed SQLite data affinity input and filtering (SELECT/WHERE usage) for such data types as
  • timestamp: text and int,
  • uuid: text(32..39) and blob(16),
  • bool: text(1..5) and int,
  • double precision, float and numeric: real values and special values with text affinity (+Infinity, -Infinity, NaN),
  • macaddr: text(12..17) or blob(6) or integer,
  • macaddr8: text(16..23) or blob(8) or integer,
  • json: text(default) or blob as SQLite jsonb object,
  • inet: text(8..49) or blob(4..5 IP v4, 16..17 IP v6) or integer (IP v4).
• Support mixed SQLite data affinity output (INSERT/UPDATE) for such data types as
  • timestamp: text(default) or int,
  • uuid: text(36) or blob(16)(default),
  • macaddr: text(17) or blob(6) or integer(default),
  • macaddr8: text(23) or blob(8) or integer(default),
  • inet: integer (default for IP v4) or blob(4..5 IP v4, 16..17 default for IP v6) or text(8..49).
• Full support for +Infinity (means ∞) and -Infinity (means -∞) special values for IEEE 754-2008 numbers in double precision, float and numeric columns including such conditions as n < '+Infinity' or m > '-Infinity'.
• Bidirectional data transformation for geometry and geography data types for SpatiaLite ↔ PostGIS. EWKB data transport is used. See GIS support description.

Pushing down

• WHERE clauses are pushdowned
• Some aggregate functions are pushdowned
• ORDER BY is pushdowned
• Joins (left/right/inner/cross/semi) are pushdowned
• CASE expressions are pushdowned.
• LIMIT and OFFSET are pushdowned when all tables in the query are foreign tables belongs to the same PostgreSQL FOREIGN SERVER object.
• Support GROUP BY, HAVING push-down.
• mod() is pushdowned. In PostgreSQL this function gives argument-dependend data type, but result from SQLite always have real affinity.
• = operator for GIS data objects is pushed down.
• Operators -> and ->> for json and jsonb are pushed down in WHERE clause.
• upper, lower and other character case functions are not pushed down because they does not work with UNICODE character in SQLite.
• WITH TIES option is not pushed down.
• Bit string # (XOR) operator is not pushed down because there is no equal SQLite operator.
• Operations with macaddr or macaddr8 data are not pushed down.
• GIS data oparators are not pushdowned except for =.

Notes about pushing down

• For push-down case, the number after floating point may be different from the result of PostgreSQL.

Notes about features

• SQLite evaluates division by zero as NULL. It is different from PostgreSQL, which will display Division by zero error.
• The data type of column of foreign table should match with data type of column in SQLite to avoid wrong result. For example, if the column of SQLite is float (which will be stored as float8), the column of foreign table should be float8, too. If the column of foreign table is float4, it may cause wrong result when SELECT.
• For key option, user needs to specify the primary key column of SQLite table corresponding with the key option. If not, wrong result may occur when UPDATE or DELETE.
• When sum function result value is out of range, sqlite_fdw will display Infinity value. It is different from PostgreSQL, which gives ERROR: value out of range: overflow error.
• For numeric data type, sqlite_fdw use sqlite3_column_double to get value, while SQLite shell uses sqlite3_column_text to get value. Those 2 APIs may return different numeric value. Therefore, for numeric data type, the value returned from sqlite_fdw may different from the value returned from SQLite shell.
• sqlite_fdw can return implementation-dependent order for column if the column is not specified in ORDER BY clause.
• When the column type is varchar array, if the string is shorter than the declared length, values of type character will be space-padded; values of type character varying will simply store the shorter string.
• String literals for boolean (t, f, y, n, yes, no, on, off etc. case insensitive) can be readed and filtred but cannot writed, because SQLite documentation recommends only int affinity values (0 or 1) for boolean data and usually text boolean data belongs to legacy datasets.
• Directry for SQLite foreign table you can use SQLite specific extractor operand for -> or ->> like $.a.d[1] in WHERE clause, but PostgreSQL will calculate result of equal expression in SELECT clause as NULL.
• If you will use unsupported by sqlite_fdw older SQLite versions from your OS, please note SQLite JSON processnig behaviour was unstable between 3.45.0 and 3.48.0 especially for negative array indexes. Please note this for explaining any unexpected results after -> or ->> operators or failed tests on your OS.

Also see Limitations

Supported platforms

sqlite_fdw was developed on Linux and should run on any reasonably POSIX-compliant system.

Installation

Package installation

For some Linux distributives internal packages with sqlite_fdw are avalilable.

• sqlite_fdw_14 rpm for CentOS 9, RHEL 9, Rocky Linux 9, AlmaLinux 9. Also there is other versions.
• sqlite_fdw git package for Arch Linux.

Source installation

Prerequisites:

• gcc
• make
• postgresql-server-dev, especially postgres.h
• libsqlite3-dev, especially sqlite.h
• libspatialite-dev only for geometry and geography data types support (SpatiaLite ↔ PostGIS) or for full tests

1. Install SQLite & Postgres Development Libraries

For Debian or Ubuntu:

apt-get install libsqlite3-dev

apt-get install postgresql-server-dev-XX, where XX matches your postgres version, i.e. apt-get install postgresql-server-dev-15

apt-get install libspatialite-dev - for SpatiaLite ↔ PostGIS transformations

Instead of system libsqlite3-dev from OS repository you can also download SQLite source code and build separate SQLite version with FTS5 for full-text search. The directory of this not OS SQLite library can be pointed as prefix in a command like ./configure --enable-fts5 --prefix=$SQLITE_FOR_TESTING_DIR before make and make install.

2. Build and install sqlite_fdw

sqlite_fdw does not require to be compiled with PostGIS and libspatialite-dev. They are used only for full tests which includes test for GIS support.

Before building please add a directory of pg_config to PATH or ensure pg_config program is accessible from command line only by the name.

Build and install without GIS support

make USE_PGXS=1
make install USE_PGXS=1

Build and install without GIS support against separate compiled and installed SQLite version placed at given path. Example for /opt/testing/other/SQLite/3.49.0.

make USE_PGXS=1 SQLITE_FOR_TESTING_DIR=/opt/testing/other/SQLite/3.49.0
make install USE_PGXS=1 SQLITE_FOR_TESTING_DIR=/opt/testing/other/SQLite/3.49.0

Build and install with GIS support

make USE_PGXS=1 ENABLE_GIS=1
make install USE_PGXS=1 ENABLE_GIS=1

Also you can build against separate SQLite version and with GIS support using obvious combination of variables.

If you want to build sqlite_fdw in a source tree of PostgreSQL, use

make
make install

You also can add ENABLE_GIS=1 for GIS support or for testing if you have got compiled PostGIS in contrib/postgis directory. Please refer PostGIS installation script for Debian/Ubuntu configuration details.

Usage

CREATE SERVER options

sqlite_fdw accepts the following options via the CREATE SERVER command:

• database as string, required, no default

  SQLite database file address.

• updatable as boolean, optional, default true

  This option can allow or disallow data modification on foreign server for all foreign objects by default. Please note, this option can be overwritten on table level or have no effect because of some filesystem restrictions, see about connection to SQLite database file and access control. This is only recommentadion of PostgreSQL foreign server owner user not to modify data in foreign server tables. For strong restriction see the next option force_readonly.

• force_readonly as boolean, optional, default false

  This option is useful if you need grant user permission to create a foreign tables on the foreign server and revoke user permission to modify any table data on this foreign server. This option with true value can disallow any write operations on foreign server table data through SQLite file readonly access mode. This option driven only by foreign server owner role can not be overwritten by any updatable option value. This is a strong restriction given by PostgreSQL foreign server owner user not to modify data in any foreign server tables. Also see about connection to SQLite database file and access control. Please note force_readonly option manage modification access not only for the main SQLite database of the FOREIGN SERVER, but also for all attached databases.

• truncatable as boolean, optional, default false

  Allows foreign tables to be truncated using the TRUNCATE command.

• keep_connections as boolean, optional, default true

  Allows to keep connections to SQLite while there is no SQL operations between PostgreSQL and SQLite.

• batch_size as integer, optional, default 1

  Specifies the number of rows which should be inserted in a single INSERT operation. This setting can be overridden for individual tables.

• integrity_check as string, optional, default full

  Integrity checks uses to ensure database file is well-formed. This option applicable only for read-write SQLite files, otherwise ignored. none - no integrity checks before connection quick - does such integrity checks as

  1. Table or index entries that are out of sequence
  2. Misformatted records
  3. Missing pages
  4. Missing or surplus index entries
  5. CHECK, and NOT NULL constraint errors
  6. Integrity of the freelist
  7. Sections of the database that are used more than once, or not at all Uses O(N) time where N is the total number of rows in the database full - does integrity checks listed for quick mode and
  8. UNIQUE constraint errors Uses O(NlogN) where N is the total number of rows in the database fkeys - - does integrity checks listed for full mode and
  9. FOREIGN KEY constraint errors

• foreign_keys as boolean, optional, default true

  This option allows to enable internal SQLite foreign keys support if there is SQL declared foreign keys in the SQLite database. Without this option some PostgreSQL data modification on a foreign table can destroy foreign key constraint in SQLite without any notice. This option is applicable only for readwrite connection to SQLite database, because readonly connections cannot modify any data in SQLite tables. This option is ignored for readonly connections.

CREATE USER MAPPING options

There is no user or password conceptions in SQLite, hence sqlite_fdw no need any CREATE USER MAPPING command. In case of any access or data modification problems please read about connection to SQLite database file and access control.

CREATE FOREIGN TABLE options

sqlite_fdw accepts the following table-level options via the CREATE FOREIGN TABLE command:

• schema_name as string, optional, default main Through sqlite_fdw_db_attach PostgreSQL function call you can attach other SQLite databases to the FOREIGN SERVER like after SQLite ATTACH DATABASE command. Any SQLite objects from this databases will have selected alias as name prefix used like PostgreSQL schema prefix. This option allows to have deal with tables from attached SQLite databases, not only with objects from default main database of FOREIGN SERVER.

• table as string, optional, no default

  SQLite table name. Use if not equal to name of foreign table in PostgreSQL. Also see about identifier case handling.

• truncatable as boolean, optional, default from the same CREATE SERVER option

  See CREATE SERVER options section for details.

• batch_size as integer, optional, default from the same CREATE SERVER option

  See CREATE SERVER options section for details.

• updatable as boolean, optional, default true

  This option can allow or disallow data modification on separate foreign table. Please note, this option can have no effect if there is foreign server option force_readonly = true or depends on filesystem context, see about connection to SQLite database file and access control.

sqlite_fdw accepts the following column-level options via the CREATE FOREIGN TABLE command:

• column_name as string, optional, no default

  This option gives the column name to use for the column on the remote server. Also see about identifier case handling.

• column_type as string, optional, no default

  Set preferred SQLite affinity for some PostgreSQL data types can be stored in different ways in SQLite (mixed affinity case). Updated and inserted values will have this affinity. Default preferred SQLite affinity for timestamp and uuid PostgreSQL data types is text.

  • Use INT value for SQLite column (epoch Unix Time) to be treated/visualized as timestamp in PostgreSQL.
  • Use BLOB value for SQLite column to be treated/visualized as uuid.

• key as boolean, optional, default false

  Indicates a column as a part of primary key or unique key of SQLite table.

Datatypes

This table represents sqlite_fdw behaviour if in PostgreSQL foreign table column some affinity of SQLite data is detected. Some details about data values support see in limitations.

• ∅ - no support (runtime error)
• ✔ - 1↔1, PostgreSQL datatype is equal to SQLite affinity
• ✔- - PostgreSQL datatype is equal to SQLite affinity, but possible out of range error
• V - transparent transformation
• V+ - transparent transformation if possible
• i - ISO:SQL transformation for some special constants
• ? - not described/not tested
• T - cast to text in SQLite utf-8 encoding, then to PostgreSQL text with current encoding of database and then transformation for text affinity if applicable

SQLite NULL affinity always can be transparent converted for a nullable column in PostgreSQL.

SQLite data processing dependend on affinity

PostgreSQL	INT	REAL	BLOB	TEXT	TEXT but empty	nearest affinity
bool	V	∅	T	i	∅	INT
bit(n)	V (n<=64)	∅	∅	∅	∅	INT
bytea	∅	∅	✔	V	?	BLOB
char(n)	?	?	T	✔-	V	TEXT
date	V	V	T	V+	NULL	?
float4	V+	✔	∅	i	NULL	REAL
float8	V+	✔	∅	i	NULL	REAL
geometry	∅	∅	V+	∅	∅	BLOB
geography	∅	∅	V+	∅	∅	BLOB
inet	✔-	∅	V (Len=4..5, 16..17)	V+	NULL	INT v4, BLOB v6
int2	✔-	?	∅	∅	NULL	INT
int4	✔-	?	∅	∅	NULL	INT
int8	✔	?	∅	∅	NULL	INT
json	∅	∅	V+	V+	∅	TEXT
jsonb	∅	∅	V+	V+	∅	BLOB
macaddr	✔-	∅	V (Len=6b)	V+	?	INT
macaddr8	✔	∅	V (Len=8b)	V+	?	INT
name	?	?	T	✔-	NULL	TEXT
numeric	V	V	T	i	NULL	REAL
text	?	?	T	✔	V	TEXT
time	V	V	T	V+	NULL	?
timestamp	V	V	T	V+	NULL	?
timestamp + tz	V	V	T	V+	NULL	?
uuid	∅	∅	V+ (Len=16b)	V+	∅	TEXT, BLOB
varchar(n)	?	?	T	✔-	V	TEXT
varbit(n)	V (n<=64)	∅	∅	∅	∅	INT

IMPORT FOREIGN SCHEMA options

sqlite_fdw supports IMPORT FOREIGN SCHEMA (PostgreSQL 9.5+) and accepts following options via the IMPORT FOREIGN SCHEMA command:

• import_default as boolean, optional, default false

  Allow borrowing default values from SQLite table DDL.

• import_not_null as boolean, optional, default true

  Allow borrowing NULL/NOT NULL constraints from SQLite table DDL.

Datatype translation rules for IMPORT FOREIGN SCHEMA

SQLite	PostgreSQL
int	bigint
char	text
clob	text
text	text
blob	bytea
real	double precision
floa	double precision
doub	double precision
datetime	timestamp
time	time
date	date
uuid	uuid
macaddr	macaddr
macaddr8	macaddr8
geometry	geometry
geography	geography
json	json
jsonb	jsonb

Note: In case of sqlite_fdw compiling without GIS support, GIS data types will be converted to bytea.

TRUNCATE support

sqlite_fdw implements the foreign data wrapper TRUNCATE API, available from PostgreSQL 14.

As SQLite does not provide a TRUNCATE command, it is simulated with a simple unqualified DELETE operation.

Actually, TRUNCATE ... CASCADE can be simulated if we create child table of SQLite with foreign keys and ON DELETE CASCADE, and then executing TRUNCATE (which will be deparsed to DELETE).

Following restrictions apply:

• TRUNCATE ... RESTART IDENTITY is not supported
• SQLite tables with foreign key references can cause errors during truncating

Connection to SQLite database file and access control

In OS sqlite_fdw works as executed code with permissions of user of PostgreSQL server. Usually it is postgres OS user.

Data read access

For succesfully connection to SQLite database file you must have at least existed and correct SQLite file readable for OS user of PostgreSQL server process. This means all directories by path to the file must be also readable (listable) for OS user of PostgreSQL server process. There are no other conditions for PostreSQL database superuser to read all of SQLite data if there are also sqlite_fdw extension in the database and FOREIGN SERVER for SQLite database file.

Data change access

Data modification access in sqlite_fdw drived by both operating system and PostgreSQL.

OS restrictions can disallow any SQLite data modifications. Hence any PostgreSQL FOREIGN SERVER or FOREIGN TABLE options or GRANTs can be absolutely not effective. In this case SQLite data modification operations allowed by PostgreSQL can cause error message from SQLite like attempt to write a readonly database with result code 8.

Full list of OS-leveled conditions of data modification access to SQLite database file

• Existed SQLite file is not corrupted by SQLite engine conditions.
• All path elements of the file are readable (listable) for OS user of PostgreSQL server process.
• The file and a directory of the file placed on readwrite filesystem. For example sqashfs is always read-only, remote sshfs can be read-only, a disk partition can be mounted in read-only mode etc.
• The file is writable for OS user of PostgreSQL server process.
• The directory of the file is writable for OS user of PostgreSQL server process because SQLite creates some temporary transaction files.

Full list of PostgreSQL-leveled conditions of data modification access to SQLite database file

• The FOREIGN SERVER of the SQLite file have no force_readonly = true option value.
• You have USAGE right GRANT for the FOREIGN SERVER.
• The FOREIGN TABLE of SQLite table have no updatable = false option value.
• If the FOREIGN TABLE have no updatable option, ensure FOREIGN SERVER have no updatable = false option value.

Generally for sqlite_fdw access management FOREIGN SERVER owner can be like remote access manager for other FDWs.

Remote access manager can block any data modififcations in remote database for remote user of a FDW. In this case SQLite have no user or separate access conceptions, hence FOREIGN SERVER owner combines remote access manager role with internal PostgreSQL roles such as FOREIGN SERVER access management.

Functions

As well as the standard sqlite_fdw_handler() and sqlite_fdw_validator() functions, sqlite_fdw provides the following user-callable utility functions:

Common FDW diagnostics

• sqlite_fdw_version(); Returns standard "version integer" as major version * 10000 + minor version * 100 + bugfix.

sqlite_fdw_version
--------------------
              20500

• sqlite_fdw_mem() Returns number of bytes of SQLite malloced but not freed (outstanding) memory.

• sqlite_fdw_sqlite_version() Returns standard "version integer" as major version * 10000 + minor version * 100 + bugfix for SQLite library used for FDW.

• sqlite_fdw_sqlite_code_source() Returns code source for SQLite library used for FDW.

Connection management

• SETOF record sqlite_fdw_get_connections(server_name text, valid bool)

• bool sqlite_fdw_disconnect(text)

  Closes connection from PostgreSQL to SQLite in the current session.

• bool sqlite_fdw_disconnect_all()

SQLite attached files (databases, schemas)

• sqlite_fdw_db_attach (server name, sqlite_db_string text, sqlite_db_alias name) Attach SQLite database file as additional remote schema for PosrgreSQL foreign server. Similar to SQLite ATTACH DATABASE ... AS ... command. Returns internal SQLite id for attached database.

• sqlite_fdw_db_detach(server name, sqlite_db_alias name) Detach SQLite database file as additional remote schema from PosrgreSQL foreign server Similar to SQLite DETACH DATABASE ... command.

• sqlite_fdw_db_list(server name) This table function show list of all attched to PostgreSQL FOREIGN SERVER SQLite databases with its aliases or so called schema names.

SELECT * FROM sqlite_fdw_db_list('sqlite_svr');
 sqlite_id | alias |              file              | readonly | txn
-----------+-------+--------------------------------+----------+------
         0 | main  | /tmp/sqlite_fdw_test/core.db   | t        | none
         2 | 試験   | /tmp/μ/проверка.db             | f        | none

Attributes: sqlite__id: internal SQLite id for attached database. This id is unique inside of PostgreSQL FOREIGN SERVER. alias: alias of SQLite database file which also is schema name for this FDW. file: address of SQLite database file. readonly: flague of readonly database from SQLite internal metadata. txn : current transaction status of the database. This attribute is useful in case if many applications works with the SQLite DB file. Possible values: none, read, write.

SQLite database file metadata

• sqlite_fdw_db_encoding(server name) Returns internal SQLite database file endoding like PRAGAMA encoding SQLite query.

• sqlite_fdw_db_schema_version(server name, sqlite_db_alias name) Returns internal SQLite database file schema version - number of changes.

• sqlite_fdw_db_user_version(server name, sqlite_db_alias name) Returns internal SQLite database file user version - number for external usage.

• sqlite_fdw_db_application_id(server name, sqlite_db_alias name) Returns internal SQLite database file application id, which allow determine subtype of SQLite database for a stable formats like OGC GeoPackage file, MBTiles tileset, TeXnicard file etc.

SQLite metadata

• sqlite_fdw_rel_list(server name, sqlite_db_alias name) Returnl all relations from SQLite database. Attributes: schema: Name of attached database hold this table. name: Name of the table itself. type: table, view, virtual, shadow. ncol: Number of columns. wr: true for a WITHOUT ROWID table. strict: true for a STRICT table.

• sqlite_fdw_table_info(server name, table name, sqlite_db_alias name) Returns full description of a table from SQLite database. Attributes: cid: Column id (numbered from left to right, starting at 0). name: Column name. type: Column declaration type. notnull: true if NOT NULL is part of column declaration. dflt_value: The default value for the column, if there is. pk: Non-zero for PK fields. col_mode: column implementation type: normal, hidden+vtbl, gen dynamic, gen stored.

• sqlite_fdw_index_list(server name, table name, sqlite_db_alias name) Returns full description of a index from SQLite database. Attributes: sqlite__id: internal SQLite code. index_name: name of the index. unique: true for unique indexes. source: c if the index was created by a CREATE INDEX statement, u if the index was created by a UNIQUE constraint, or pk if the index was created by a PRIMARY KEY constraint. partial: true for partial idexes.

SQLite journal

• sqlite_fdw_db_journal_mode(server name, sqlite_db_alias name) Returns journal mode of the SQLite database.

• sqlite_fdw_db_journal_size_limit(server name, sqlite_db_alias name) Returns journal size limit in bytes.

• sqlite_fdw_db_synchronous_mode(server name, sqlite_db_alias name) Returns text synchronous mode of the SQLite database. Possible values off, on, normal, full, extra.

SQLite foreign keys

• sqlite_fdw_db_fkeys(server name, sqlite_db_alias name) Returns true if there is support of SQLite foreign keys in the SQLite database.

• sqlite_fdw_db_fkeys_list(server name, sqlite_db_alias name) This table function returls list of SQLite foreign keys. Attributes: i: j: source__table: column__name: constraint: onupd: ON UPDATE SQL action. ondel: ON DELETE SQL action. f8: reserved field

• sqlite_fdw_db_fkeys_check(server name, sqlite_db_alias name) This table function returns metadata about FOREIGN KEY constraint problems Attributes: source__table: Name of the table that contains the REFERENCES clause. rowid: Rowid of the row that contains the invalid REFERENCES clause, or NULL if the child table is a WITHOUT ROWID table. fk_from_table: Name of the table that is referred to. i: Index of the specific foreign key constraint that failed. The same integer as the first column in the output of the foreign_key_list SQLite pragma.

SQLite file pages

• sqlite_fdw_db_page_size(server name, sqlite_db_alias name) Returns page size of the SQLite database in bytes.

• sqlite_fdw_db_cache_spill(server name, sqlite_db_alias name) Returns cache spill in pages.

• sqlite_fdw_db_max_page_count(server name, sqlite_db_alias name) Returns maximal page count in database in pages.

• sqlite_fdw_db_cache_size(server name, sqlite_db_alias name) Returns cache size in pages.

SQLite WAL and temporary storage

• sqlite_fdw_db_tmp_directory(server name, sqlite_db_alias name) Returns temporary storage directory if not default.

• sqlite_fdw_db_temp_store(server name, sqlite_db_alias name) Returns temporary storage mode of the SQLite database. Possible values: default, memory, file

• sqlite_fdw_db_wal_checkpoint(server name, sqlite_db_alias name) This table function returns SQLite WAL checkpoint status. Attributes: blocked: true if a RESTART or FULL or TRUNCATE checkpoint was blocked from completing, for example because another thread or process was actively using the database. In other words, true if the equivalent call to sqlite3_wal_checkpoint_v2() would have returned SQLITE_BUSY. mod_pages_in_wal: Number of modified pages that have been written to the write-ahead log file. pages_suc_wr_to_db: Number of pages in the write-ahead log file that have been successfully moved back into the database file at the conclusion of the checkpoint.

Other diagnostic SQLite PRAGMAs

• sqlite_fdw_db_secure_delete(server name, sqlite_db_alias name) Returns secure delete mode code of the SQLite database.

• sqlite_fdw_db_exclusive_locking_mode(server name, sqlite_db_alias name) Returns true is case of exclusive locking mode or false in case of normal.

• sqlite_fdw_db_auto_vacuum(server name, sqlite_db_alias name) Returns auto vacuum mode of the SQLite database.

Identifier case handling

PostgreSQL folds identifiers to lower case by default, SQLite is case insensitive by default only for uppercase and lowercase ASCII base latin letters. It's important to be aware of potential issues with table and column names.

Following SQL isn't correct for SQLite: Error: duplicate column name: a, but is correct for PostgreSQL

	CREATE TABLE T (
	  "A" INTEGER,
	  "a" NUMERIC
	);

Following SQLs is correct for both SQLite and PostgreSQL because there is no column with names composed from ASCII base latin letters only.

	CREATE TABLE T_кир (
	  "А" INTEGER,
	  "а" NUMERIC
	);
	CREATE TABLE T_ελλ (
	  "Α" INTEGER,
	  "α" NUMERIC
	);
	CREATE TABLE T_dia (
	  "Ä" INTEGER,
	  "ä" NUMERIC
	);

For SQLite there is no difference between

	SELECT * FROM t;   -- №1
	SELECT * FROM T;   -- №2
	SELECT * FROM "t"; -- №3
	SELECT * FROM "T"; -- №4

For PostgreSQL the query with comment №4 is independend query to table T, not to table t as other queries. Please note this table name composed from ASCII base latin letters only. This is not applicable for other alphabet systems or mixed names. This is because toLower operation in PostgreSQL is Unicode operation but ASCII only operation in SQLite, hence other characters will not be changed.

	SELECT * FROM т;   -- №5
	SELECT * FROM Т;   -- №6
	SELECT * FROM "т"; -- №7
	SELECT * FROM "Т"; -- №8

In this case for PostgreSQL the query with comment №8 is independend query to table Т, not to table т as other queries. But for SQLite the queries with comments №6 and №8 belongs to table Т, and the queries with comments №5 and №7 belongs to table т.

If there is

	CREATE TABLE T (
	  A INTEGER,
	  b REAL
	);

in SQLite, both a and A , b and B columns will have the same real datasource in SQLite in follow foreign table:

	CREATE FOREIGN TABLE "SQLite test" (
	  "A" int4 NULL,
	  "B" float8 NULL,
	  "a" int8 NULL,
	  "b" numeric NULL
	)
	SERVER sqlite_server
	OPTIONS (table 'T');

Generated columns

SQLite provides support for generated columns. Behaviour of sqlite_fdw with this columns isn't yet described.

Note that while sqlite_fdw will INSERT or UPDATE the generated column value in SQLite, there is nothing to stop the value being modified within SQLite, and hence no guarantee that in subsequent SELECT operations the column will still contain the expected generated value. This limitation also applies to postgres_fdw.

For more details on generated columns see:

• Generated Columns
• CREATE FOREIGN TABLE

Character set handling

There is no character set metadata stored in SQLite, only PRAGMA encoding; with UTF-only values (UTF-8, UTF-16, UTF-16le, UTF-16be). SQLite text output function guarantees UTF-8 encoding.

When sqlite_fdw connects to a SQLite, all strings are interpreted acording the PostgreSQL database's server encoding. It's not a problem if your PostgreSQL database encoding belongs to Unicode family. Otherewise interpretation transformation problems can occur. Some unproper for PostgreSQL database encoding characters will cause error like character with byte sequence 0x** in encoding "UTF8" has no equivalent in encoding "**".

Character case functions such as upper, lower and other are not pushed down because they does not work with Unicode character in SQLite.

Sqlite_fdw tested with PostgreSQL database encodings EUC_JP, EUC_KR, ISO_8859_5, ISO_8859_6, ISO_8859_7, ISO_8859_8, LATIN1, LATIN2, LATIN3, LATIN4, LATIN5, LATIN6, LATIN7, LATIN8, LATIN9, LATIN9, LATIN10, WIN1250, WIN1251, WIN1252, WIN1253, WIN1254, WIN1255, WIN1256, WIN1257 and it's synomyms. Some other encodings also can be supported, but not tested.

Examples

Install the extension:

Once for a database you need, as PostgreSQL superuser.

	CREATE EXTENSION sqlite_fdw;

Create a foreign server with appropriate configuration:

Once for a foreign datasource you need, as PostgreSQL superuser. Please specify SQLite database file path using database option.

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

Grant usage on foreign server to normal user in PostgreSQL:

Once for a normal user (non-superuser) in PostgreSQL, as PostgreSQL superuser. It is a good idea to use a superuser only where really necessary, so let's allow a normal user to use the foreign server (this is not required for the example to work, but it's secirity recomedation).

	GRANT USAGE ON FOREIGN SERVER sqlite_server TO pguser;

Where pguser is a sample user for works with foreign server (and foreign tables).

User mapping

There is no user or password conceptions in SQLite, hence sqlite_fdw no need any CREATE USER MAPPING command. About access problems see in CREATE USER MAPPING options.

Create foreign table

All CREATE FOREIGN TABLE SQL commands can be executed as a normal PostgreSQL user if there were correct GRANT USAGE ON FOREIGN SERVER. No need PostgreSQL supersuer for secirity reasons but also works with PostgreSQL supersuer.

Please specify table option if SQLite table name is different from foreign table name.

	CREATE FOREIGN TABLE t1 (
	  a integer,
	  b text
	)
	SERVER sqlite_server
	OPTIONS (
	  table 't1_sqlite'
	);

If you want to update tables, please add OPTIONS (key 'true') to a primary key or unique key like the following:

	CREATE FOREIGN TABLE t1(
	  a integer OPTIONS (key 'true'),
	  b text
	)
	SERVER sqlite_server
	OPTIONS (
	  table 't1_sqlite'
	);

If you need to convert INT SQLite column (epoch Unix Time) to be treated/visualized as TIMESTAMP in PostgreSQL, please add OPTIONS (column_type 'INT') when defining FOREIGN table at PostgreSQL like the following:

	CREATE FOREIGN TABLE t1(
	  a integer,
	  b text,
	  c timestamp without time zone OPTIONS (column_type 'INT')
	)
	SERVER sqlite_server
	OPTIONS (
	  table 't1_sqlite'
	);

As above, but with aliased column names:

	CREATE FOREIGN TABLE t1(
	  a integer,
	  b text OPTIONS (column_name 'test_id'),
	  c timestamp without time zone OPTIONS (column_type 'INT', column_name 'unixtime')
	)
	SERVER sqlite_server
	OPTIONS (
	  table 't1_sqlite'
	);

Import a SQLite database as schema to PostgreSQL:

	IMPORT FOREIGN SCHEMA main
	FROM SERVER sqlite_server
	INTO public;

Note: main means default SQLite database with address from FOREIGN SERVER option. You can connect to other SQLite databases as schemas after sqlite_fdw_db_attach function calling. Please note, all attached databases can be detaced after IMPORT FOREIGN SCHEMA. Please verify and attach again.

Access foreign table

For the table from previous examples

	SELECT * FROM t1;

Limitations

SQL commands

• COPY command for foreign tables is not supported
• IMPORT of generated column is not supported
• INSERT into a partitioned table which has foreign partitions is not supported. Error Not support partition insert will display.
• TRUNCATE in sqlite_fdw always delete data of both parent and child tables (no matter user inputs TRUNCATE table CASCADE or TRUNCATE table RESTRICT) if there are foreign-keys references with ON DELETE CASCADE clause.
• RETURNING is not supported.

Mixed affinity support

SQLite text affinity values which is different for SQLite unique checks can be equal for PostgreSQL because sqlite_fdw unifyes semantics of values, not storage form. For example 1(integer), Y(text) and tRuE(text) SQLite values is different in SQLite but equal in PostgreSQL as true values of boolean column. This is also applicable for a data with text affinity in uuid, timestamp, double precision, float and numeric columns of foreign tables. Please be carefully if you want to use mixed affinity column as PostgreSQL foreign table primary key.

Arrays

Array support is experimental. Please be careful.

• sqlite_fdw only supports ARRAY const, for example, ANY (ARRAY[1, 2, 3]) or ANY ('{1, 2 ,3}').
• sqlite_fdw does not support ARRAY expression, for example, ANY (ARRAY[c1, 1, c1+0]).
• For ANY(ARRAY) clause, sqlite_fdw deparses it using IN operator.

Numbers (range and precision)

• For sum function of SQLite, output of sum(bigint) is integer value. If input values are big, the overflow error may occurs on SQLite because it overflow within the range of signed 64bit. For PostgreSQL, it can calculate as over the precision of bigint, so overflow does not occur.
• SQLite promises to preserve the 15 most significant digits of a floating point value. The big value which exceed 15 most significant digits may become different value after inserted.
• SQLite does not support numeric type as PostgreSQL. Therefore, it does not allow to store numbers with too high precision and scale. Error out of range occurs.
• SQLite does not support NaN special value for IEEE 754-2008 numbers. Please use this special value very cerefully because there is no such conception in SQLite at all and NaN value treated in SQLite as NULL.
• SQLite support +Infinity and -Infinity special values for IEEE 754-2008 numbers in SQL expressions with numeric context. This values can be readed with both text and real affiniy, but can be writed to SQLite only with real affinity (as signed out of range value 9.0e999).
• Please note you can turn off processing of IEEE 754-2008 values with text affiniy thorough real value of column_type option. This can increase SELECT or ORDER speed, becasuse there will be no normalize function wrapping, but in this case any query will have unsuccessfilly result in case of any value with text affiniy.

Boolean values

• sqlite_fdw boolean values support exists only for bool columns in foreign table. SQLite documentation recommends to store boolean as value with integer affinity. NULL isn't converted, 1 converted to true, all other NOT NULL values converted to false. During SELECT ... WHERE condition_column condition converted only to condition_column.
• sqlite_fdw don't provides limited support of boolean values if bool column in foreign table mapped to SQLite text affinity.

UUID values

• sqlite_fdw UUID values support exists only for uuid columns in foreign table. SQLite documentation recommends to store UUID as value with both blob and text affinity. sqlite_fdw can pushdown both reading and filtering both text and blob values.
• Expected affinity of UUID value in SQLite table determined by column_type option of the column for INSERT and UPDATE commands. PostgreSQL supports both blob and text affinity.
• Usual form of UUID from a value with blob affinity can be generated with such SQLite query as

select case when typeof(u) = 'blob' then
	substr(lower(hex(u)),1,8) || '-' ||
	substr(lower(hex(u)),9,4) || '-' ||
	substr(lower(hex(u)),13,4) || '-' ||
	substr(lower(hex(u)),17,4) || '-' ||
	substr(lower(hex(u)),21,12)
	else null end uuid_canon
from "type_UUID";

bit and varbit support

• sqlite_fdw PostgreSQL bit/varbit values support based on int SQLite data affinity, because there is no per bit operations for SQLite blob affinity data. Maximum SQLite int affinity value is 8 bytes length, hence maximum bit/varbit values length is 64 bits.
• sqlite_fdw doesn't pushdown # (XOR) operator because there is no equal SQLite operator.

MAC address support

• sqlite_fdw PostgreSQL macaddr/macaddr8 values support based on int SQLite data affinity, because there is no per bit operations for SQLite blob affinity data. For macaddr out of range error is possible because this type is 6 bytes length, but SQLite int can store value up to 8 bytes.
• sqlite_fdw doesn't pushdown any operations with MAC adresses because there is 3 possible affinities for it in SQLite: integer, blob and text.

IP address support

• sqlite_fdw PostgreSQL inet values support based on int SQLite data affinity for IP v4 and blob SQLite data affinity for IP v6.
• Usual form of IP v4 address with cidr from a value with integer affinity can be generated with such SQLite query as

select  case when typeof(ip) = 'integer'
		then ((ip >> 24) & 255) || '.' || ((ip >> 16) & 255) || '.' || ((ip >> 8) & 255) || '.' || (ip & 255) ||
			case when (ip >> 32) > 0 then '/' || (ip >> 32) else '' end
		else null
		end ipv4_text,
		ip
from "type_INET";

• Usual form of IP v6 or IP v4 address from a value with blob affinity can be generated with such SQLite query as

select
    case
        when typeof(ip) = 'blob' and (length(ip) = 16 or length(ip) = 17) then
            lower(
                substr(hex(ip),1,4)  || ':' ||
                substr(hex(ip),5,4)  || ':' ||
                substr(hex(ip),9,4)  || ':' ||
                substr(hex(ip),13,4) || ':' ||
                substr(hex(ip),17,4) || ':' ||
                substr(hex(ip),21,4) || ':' ||
                substr(hex(ip),25,4) || ':' ||
                substr(hex(ip),29,4)
            ) ||
            case
                when length(ip) = 17 then
                    '/' || ((instr('123456789ABCDEF', substr(hex(ip),33,1)) << 4) + instr('123456789ABCDEF', substr(hex(ip),34,1)))
                else ''
            end
        when typeof(ip) = 'blob' and (length(ip) = 4 or length(ip) = 5) then
                ((instr('123456789ABCDEF', substr(hex(ip),1,1)) << 4) + instr('123456789ABCDEF', substr(hex(ip),2,1))) || '.' ||
                ((instr('123456789ABCDEF', substr(hex(ip),3,1)) << 4) + instr('123456789ABCDEF', substr(hex(ip),4,1))) || '.' ||
                ((instr('123456789ABCDEF', substr(hex(ip),5,1)) << 4) + instr('123456789ABCDEF', substr(hex(ip),6,1))) || '.' ||
                ((instr('123456789ABCDEF', substr(hex(ip),7,1)) << 4) + instr('123456789ABCDEF', substr(hex(ip),8,1)))
            ||
            case
                when length(ip) = 5 then
                    '/' || ((instr('123456789ABCDEF', substr(hex(ip),9,1)) << 4) + instr('123456789ABCDEF', substr(hex(ip),10,1)))
                else ''
            end
        else null
    end as ip_text
from "type_INET";

• IP address with possible cidr addition enconing as SQLite BLOB value based on such conventions

 m - mask as number of bits
 a - bytes of IP address

 IP v4 + cidr
┏━━━┳━━━┳━━━┳━━━┳━━━┓
┃ a ┃ a ┃ a ┃ a ┃ m ┃
┗━━━┻━━━┻━━━┻━━━┻━━━┛
  0   1   2   3   4  - byte index
 IP v6 + cidr
┏━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┓
┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ m ┃
┗━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┛
  0   1   2   3   4   5   5   7   8   9   10  11  12  13  14  15  16  17 - byte index

 IP v4
┏━━━┳━━━┳━━━┳━━━┓
┃ a ┃ a ┃ a ┃ a ┃
┗━━━┻━━━┻━━━┻━━━┛
  0   1   2   3  - byte index
 IP v6
┏━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┓
┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃
┗━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┛
  0   1   2   3   4   5   5   7   8   9   10  11  12  13  14  15  16 - byte index

• IP address v4 with possible cidr addition enconing as SQLite integer based on such conventions

 m - mask as number of bits
 a - IP address bytes

 IP v4 + cidr
┏━━━┳━━━┳━━━┳━━━┳━━━┓
┃ m ┃ a ┃ a ┃ a ┃ a ┃
┗━━━┻━━━┻━━━┻━━━┻━━━┛
  0   1   2   3   4  - byte index

 IP v4
┏━━━┳━━━┳━━━┳━━━┓
┃ a ┃ a ┃ a ┃ a ┃
┗━━━┻━━━┻━━━┻━━━┛
  0   1   2   3  - byte index

• sqlite_fdw doesn't pushdown any operations with IP adresses because there is 3 possible affinities for it in SQLite: integer, blob and text.

JSON support and operators

• Operators -> and ->> for json and jsonb are pushed down. This means if you deal with a foreign table only, you can use SQLite syntax of -> and ->> operators which is more rich than PostgreSQL syntax. In PostgreSQL this operators means only 1-leveled extraction after one call, but possible multilevel extraction in one call of the operator in SQLite. You can extract '{"a": 2, "c": [4, 5, {"f": 7}]}' ->'c' -> 2 with result {"f":7} both for PostgreSQL and SQLite tables, but '{"a": 2, "c": [4, 5, {"f": 7}]}' ->'$.c[2]' possible only in SQLite and for a foreign table.
• For PostgreSQL numeric argument of -> and ->> operators means only coordinate inside of array. In SQLite transformable to number text argument of this operators also can extract array element. PostgreSQL differs json -> (2::text) and json -> 2, but SQLite not: json -> '2'.
• Please note you can turn off processing of normalizing possible SQLite json values with text affinity for a column with formal SQLite json data type as option column_type = text. This can increase SELECT or ORDER speed, because there will be no normalize function wrapping, but in this case any query will have unsuccessfully result in case of any value with blob affiniy including any possible SQLite jsonb value.

-- a query with normalization - standard ISO:SQL behaviour
EXPLAIN (VERBOSE, COSTS OFF)
SELECT "i", "j", j."j"->'c' res FROM "type_JSON" j;
                         QUERY PLAN
-------------------------------------------------------------
 Foreign Scan on public."type_JSON" j
   Output: i, j, (j -> 'c'::text)
   SQLite query: SELECT `i`, json(`j`) FROM main."type_JSON"
(3 rows)

-- turn off normalization
ALTER FOREIGN TABLE "type_JSON" ALTER COLUMN j OPTIONS (ADD column_type 'text');
EXPLAIN (VERBOSE, COSTS OFF)
SELECT "i", "j", j."j"->'c' res FROM "type_JSON" j;
                      QUERY PLAN
-------------------------------------------------------
 Foreign Scan on public."type_JSON" j
   Output: i, j, (j -> 'c'::text)
   SQLite query: SELECT `i`, `j` FROM main."type_JSON"
(3 rows)

Tests

Test directory have structure as following:

+---sql
|   +---13.15
|   |       filename1.sql
|   |       filename2.sql
|   |
|   +---14.12
|   |       filename1.sql
|   |       filename2.sql
|   |
.................
|   \---17.0
|          filename1.sql
|          filename2.sql
|
\---expected
|   +---13.15
|   |       filename1.out
|   |       filename2.out
|   |
|   +---14.12
|   |       filename1.out
|   |       filename2.out
|   |
.................
|   \---17.0
            filename1.out
            filename2.out

The test cases for each version are based on the test of corresponding version of PostgreSQL. You can execute test by test.sh directly. The version of PostgreSQL is detected automatically by $(VERSION) variable in Makefile. The corresponding sql and expected directory will be used to compare the result. For example, for Postgres 15.0, you can execute "test.sh" directly, and the sql/15.0 and expected/15.0 will be used to compare automatically. Please don't forget a command like export SQLITE_FOR_TESTING_DIR= with the same path as in SQLite's ./configure --prefix berfore testing if you want to test not against your OS SQLite version, but against separate downloaded, compiled and installed SQLite version.

Test data directory is /tmp/sqlite_fdw_test. If you have /tmp mounted as tmpfs the tests will be up to 800% faster.

Contributing

Opening issues and pull requests on GitHub are welcome. For pull request, please make sure these items below for testing:

• Create test cases (if needed) for the latest version of PostgreSQL supported by sqlite_fdw. All error testcases should have a comment about test purpose.
• Execute test cases and update expectations for the latest version of PostgreSQL
• Test creation and execution for other PostgreSQL versions are welcome but not required.

Preferred code style see in PostgreSQL source codes. For example

type
funct_name (type arg ...)
{
	t1 var1 = value1;
	t2 var2 = value2;

	for (;;)
	{
	}
	if ()
	{
	}
}

To debug, you need to build PostgreSQL in debug mode. Use the following options.

./configure --prefix=<path_to_postgresql_build_folder> --enable-cassert --enable-debug CFLAGS="-ggdb -O0 -g3 -fno-omit-frame-pointer"

Also please refer https://wiki.postgresql.org/wiki/Developer_FAQ#What_debugging_features_are_available.3F

Useful links

Source

• https://github.com/pgspider/sqlite_fdw
• https://pgxn.org/dist/sqlite_fdw/

General FDW Documentation

• https://www.postgresql.org/docs/current/ddl-foreign-data.html
• https://www.postgresql.org/docs/current/sql-createforeigndatawrapper.html
• https://www.postgresql.org/docs/current/sql-createforeigntable.html
• https://www.postgresql.org/docs/current/sql-importforeignschema.html
• https://www.postgresql.org/docs/current/fdwhandler.html
• https://www.postgresql.org/docs/current/postgres-fdw.html

Other FDWs

• https://wiki.postgresql.org/wiki/Fdw
• https://pgxn.org/tag/fdw/

License

• Copyright © 2018, TOSHIBA CORPORATION
• Copyright © 2011 - 2016, EnterpriseDB Corporation

Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.

See the License file for full details.