Contents

• NOTE: Please don't use this release because it is released as version 2.0.0.
• SQLite Foreign Data Wrapper for PostgreSQL
  • Installation
    • 1. Install SQLite library
    • 2. Build and install sqlite_fdw
  • Usage
    • Load extension
    • Create server
    • Create foreign table
    • Import foreign schema
    • Access foreign table
  • Features
  • Limitations
  • Contributing
  • License

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.

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 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
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 file for full details.