# NOTE: Please don't use this release because it is released as version 2.0.0. # SQLite Foreign Data Wrapper for PostgreSQL This PostgreSQL extension is a Foreign Data Wrapper for [SQLite][1]. The current version can work with PostgreSQL 9.6, 10, 11, 12 and 13. ## Installation ### 1. Install SQLite library For debian or ubuntu:
apt-get install libsqlite3-dev
You can also [download SQLite source code][2] and [build SQLite][3]. ### 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
make install
## Usage ### Load extension
CREATE EXTENSION sqlite_fdw;
### Create server Please specify SQLite database path using `database` option:
CREATE SERVER sqlite_server FOREIGN DATA WRAPPER sqlite_fdw OPTIONS (database '/tmp/test.db');
### Create foreign table 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');
### Import foreign schema
IMPORT FOREIGN SCHEMA public FROM SERVER sqlite_server INTO public;
### Access foreign table
SELECT * FROM t1;
## Features - Support update to foreign table - WHERE clauses are pushdowned - Aggregate function are pushdowned - Order By is pushdowned - Joins (left/right/inner) are pushdowned - Limit and Offset are pushdowned (*when all tables queried are fdw) - Transactions ## Limitations - `COPY` command for foreign tables is not supported - Insert into a partitioned table which has foreign partitions is not supported ## Contributing Opening issues and pull requests on GitHub are welcome. ## License Copyright (c) 2017 - 2021, 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`][4] file for full details. [1]: https://www.sqlite.org/index.html [2]: https://www.sqlite.org/download.html [3]: https://www.sqlite.org/howtocompile.html [4]: LICENSE