Skip to content


Switch branches/tags

Name already in use

A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?

Latest commit

* Add human readable error message
* separate function for a disallowed SQLite affinity with a PostgreSQL column data type.
* `NullableDatum` for PG 11 in sqlite_fdw.h

Git stats


Failed to load latest commit information.
Latest commit message
Commit time
February 10, 2021 09:35
September 24, 2021 19:31
September 26, 2022 16:51
January 13, 2023 10:13
September 26, 2022 16:51
May 8, 2023 16:04
September 26, 2022 16:51
September 24, 2021 19:31
September 24, 2021 19:31
November 6, 2020 16:29
September 26, 2022 16:51

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 11, 12, 13, 14, 15 and confirmed with SQLite 3.38.5.

PostgreSQL + SQLite


  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


Common features

  • Transactions
  • Support INSERT/UPDATE/DELETE (both Direct modification and Foreign modification).
  • 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


  • WHERE clauses are pushdowned
  • Aggregate function are pushdowned
  • ORDER BY is pushdowned
  • Joins (left/right/inner/cross) are pushdowned
  • CASE expressions are pushdowned.
  • LIMIT and OFFSET are pushdowned (*when all tables queried are fdw)
  • Support GROUP BY, HAVING push-down.
  • 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.

Notes about pushdowning

  • 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 of data in table is out of range, sqlite_fdw will display Infinity value. It is different from PostgreSQL FDW, which will display 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.

Also see Limitations

Supported platforms

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


For some Linux distributives deb and rpm packages are avalillable.

Source installation


  • libsqlite3-dev, especially sqlite.h
  • postgresql-server-dev, especially postgres.h
  • gcc
  • make

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

You can also download SQLite source code and build SQLite.

2. Build and install sqlite_fdw

Add a directory of pg_config to PATH and build and install sqlite_fdw.

make USE_PGXS=1
make install USE_PGXS=1

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

make install



sqlite_fdw accepts the following options via the CREATE SERVER command:

  • database as string, required

    SQLite database path.

  • truncatable as boolean, optional

    Allows foreign tables to be truncated using the TRUNCATE command.

  • keep_connections as boolean, optional

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

  • batch_size as integer, optional

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


There is no user or password conceptions in SQlite, hence sqlite_fdw no need any CREATE USER MAPPING command.

In OS sqlite_fdw works as executed code with permissions of user of PostgreSQL server. Usually it is postgres OS user. For interacting with SQLite database without access errors ensure this user have follow permissions:

  • read permission on all directories by path to the SQLite database file;
  • read permission on SQLite database file;
  • write permissions both on SQLite database file and directory it contains if you need a modification. During INSERT, UPDATE or DELETE in SQLite database, SQLite engine functions makes temporary files with transaction data in the directory near SQLite database file. Hence without write permissions you'll have a message failed to execute remote SQL: rc=8 attempt to write a readonly database.


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

  • table as string, optional

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

  • truncatable as boolean, optional

    Allows table to be truncated using the TRUNCATE command.

  • batch_size as integer, optional

    See CREATE SERVER options section for details.

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

  • column_name as string, optional

    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

    Option to convert INT SQLite column (epoch Unix Time) to be treated/visualized as TIMESTAMP in PostgreSQL.

  • key as boolean, optional

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


sqlite_fdw supports IMPORT FOREIGN SCHEMA (PostgreSQL 9.5+) and accepts no custom options for this command.

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


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

  • 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()

  • SETOF record sqlite_fdw_version();

Identifier case handling

PostgreSQL folds identifiers to lower case by default, SQlite is case insensetive by default. It's important to be aware of potential issues with table and column names.

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

	  "a" 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.

If there is

	  b REAL

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

	  "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:

Character set handling

When sqlite_fdw connects to a SQLite no character set metadata stored in SQLite. There is only PRAGMA encoding; with UTF-only values (UTF-8, UTF-16, UTF-16le, UTF-16be). All strings are interpreted acording the PostgreSQL database's server encoding. It's not a problem if both PostgreSQL database and SQLite character data from database file has UTF-8 or UTF-16 encoding. Otherewise character interpretation transformation problems will occur.

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


Install the extension:

Once for a database you need, as PostgreSQL superuser.


Create a foreign server with appropriate configuration:

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

	CREATE SERVER sqlite_server
          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.

	  a integer,
	  b text
	SERVER sqlite_server
	  table 't1_sqlite'

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

	  a integer OPTIONS (key 'true'),
	  b text
	SERVER sqlite_server 
	  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:

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

As above, but with aliased column names:

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

Import a SQLite database as schema to PostgreSQL:

	FROM SERVER sqlite_server
	INTO public;

Note: someschema has no particular meaning and can be set to an arbitrary value.

Access foreign table

For the table from previous examples



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.


  • 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 special values for IEEE 754-2008 numbers such as NaN, +Infinity and -Infinity in SQL expressions with numeric context. Also SQLite can not store this values with real affinity. In opposite to SQLite, PostgreSQL can store special values in columns belongs to real datatype family such as float or double precision and use arithmetic comparation for this values. In oppose to PostgreSQL, SQLite stores NaN, +Infinity and -Infinity as a text values. Also conditions with special literals (such as n < '+Infinity' or m > '-Infinity' ) isn't numeric conditions in SQLite and gives unexpected result after pushdowning in oppose to internal PostgreSQL calculations. During INSERT INTO ... SELECT or in WHERE conditions sqlite_fdw uses given by PostgreSQL standard case sensetive literals only in follow forms: NaN, -Infinity, Infinity, not original strings from WHERE condition. This can caused selecting issues.

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 NON 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.


Test directory have structure as following:

|   +---11.7
|   |       filename1.sql
|   |       filename2.sql
|   | 
|   +---12.12
|   |       filename1.sql
|   |       filename2.sql
|   | 
|   \---15.0
|          filename1.sql
|          filename2.sql
|   +---11.7
|   |       filename1.out
|   |       filename2.out
|   | 
|   +---12.12
|   |       filename1.out
|   |       filename2.out
|   | 
|   \---15.0

The test cases for each version are based on the test of corresponding version of PostgreSQL. You can execute test by 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 "" directly, and the sql/15.0 and expected/15.0 will be used to compare automatically.


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.
  • 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.

Useful links


General FDW Documentation

Other FDWs


Copyright (c) 2018, TOSHIBA CORPORATION Copyright (c) 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.