This is a foreign data wrapper (FDW) to connect PostgreSQL to SQLite database file. This FDW works with PostgreSQL 12, 13, 14, 15, 16 and confirmed with SQLite 3.42.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.
- Features
- Supported platforms
- Installation
- Usage
- Functions
- Identifier case handling
- Generated columns
- Character set handling
- Examples
- Limitations
- Tests
- Contributing
- Useful links
- Transactions
- Support
INSERT
/UPDATE
/DELETE
(both Direct modification and Foreign modification), see access control about conditions of succesfully data modification. - Support
TRUNCATE
by deparsing intoDELETE
statement withoutWHERE
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 usingbatch_size
option - Support
INSERT
/UPDATE
with generated column - Support
ON CONFLICT DO NOTHING
- Support mixed SQLite data affinity input and filtering (
SELECT
/WHERE
usage) for such data types astimestamp
:text
andint
,uuid
:text
(32..39) andblob
(16),bool
:text
(1..5) andint
,double precision
,float
andnumeric
:real
values and special values withtext
affinity (+Infinity
,-Infinity
,NaN
).
- Support mixed SQLite data affinity output (
INSERT
/UPDATE
) for such data types astimestamp
:text
(default) orint
,uuid
:text
(36) orblob
(16)(default).
- Full support for
+Infinity
(means ∞) and-Infinity
(means -∞) special values for IEEE 754-2008 numbers indouble precision
,float
andnumeric
columns including such conditions asn < '+Infinity'
orm > '-Infinity'
. - Bidirectional data transformation for
geometry
andgeography
data types for SpatiaLite ↔ PostGIS. EWKB data transport is used. See GIS support description.
WHERE
clauses are pushdowned- Aggregate function are pushdowned
ORDER BY
is pushdowned- Joins (left/right/inner/cross) are pushdowned
CASE
expressions are pushdowned.LIMIT
andOFFSET
are pushdowned when all tables in the query are foreign tables belongs to the same PostgreSQLFOREIGN 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 havereal
affinity.=
operator for GIS data objects are pushdowned.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. - operators for GIS data objects are not pushdowned except to
=
.
- For push-down case, the number after floating point may be different from the result of PostgreSQL.
- SQLite evaluates division by zero as
NULL
. It is different from PostgreSQL, which will displayDivision 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 asfloat8
), the column of foreign table should befloat8
, too. If the column of foreign table isfloat4
, it may cause wrong result whenSELECT
. - For
key
option, user needs to specify the primary key column of SQLite table corresponding with thekey
option. If not, wrong result may occur whenUPDATE
orDELETE
. - When
sum
function result value is out of range,sqlite_fdw
will displayInfinity
value. It is different from PostgreSQL, which givesERROR: value out of range: overflow
error. - For
numeric
data type,sqlite_fdw
usesqlite3_column_double
to get value, while SQLite shell usessqlite3_column_text
to get value. Those 2 APIs may return different numeric value. Therefore, fornumeric
data type, the value returned fromsqlite_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 inORDER 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 typecharacter 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 onlyint
affinity values (0
or1
) for boolean data and usually text boolean data belongs to legacy datasets.
Also see Limitations
sqlite_fdw
was developed on Linux and should run on any
reasonably POSIX-compliant system.
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.
Prerequisites:
gcc
make
postgresql-server-dev
, especiallypostgres.h
libsqlite3-dev
, especiallysqlite.h
libspatialite-dev
only for geoinformational data types support (SpatiaLite ↔ PostGIS) or full tests
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 libsqlite3-dev
you can also download SQLite source code and build SQLite with FTS5 for full-text search.
You can compile without libspatialite-dev
and GIS support. This library is necessary only for full tests. PostGIS is not necessary for sqlite_fdw
compilation anyway and also used only for full tests.
Before building please add a directory of pg_config
to PATH or ensure pg_config
program is accessable 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 with GIS support
make USE_PGXS=1 ENABLE_GIS=1
make install USE_PGXS=1 ENABLE_GIS=1
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.
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 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 anyupdatable
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 Connection to SQLite database file and access control. -
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.
There is no user or password conceptions in SQLite, hence sqlite_fdw
no need any CREATE USER MAPPING
command.
About access model and possible data modifications problems see about connection to SQLite database file and access control.
sqlite_fdw
accepts the following table-level options via the
CREATE FOREIGN TABLE
command:
-
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
optionSee
CREATE SERVER
options section for details. -
batch_size as integer, optional, default from the same
CREATE SERVER
optionSee
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
anduuid
PostgreSQL data types istext
.- Use
INT
value for SQLite column (epoch Unix Time) to be treated/visualized astimestamp
in PostgreSQL. - Use
BLOB
value for SQLite column to be treated/visualized asuuid
.
- Use
-
key as boolean, optional, default false
Indicates a column as a part of primary key or unique key of SQLite table.
WARNING! The table below represents roadmap, work still in progress. Until it will be ended please refer real behaviour in non-obvious cases, where there is no ✔ or ∅ mark.
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)
- V - transparent transformation
- T - cast to text in SQLite utf-8 encoding, then to PostgreSQL text with current encoding of database and then transparent transformation if applicable
- ✔ - transparent transformation where PostgreSQL datatype is equal to SQLite affinity
- V+ - transparent transformation if appliacable
- ? - not described/not tested
- - - transparent transformation is possible for PostgreSQL (always or for some special values), but not implemented in
sqlite_fdw
.
SQLite NULL
affinity always can be transparent converted for a nullable column in PostgreSQL.
PostgreSQL | SQLite INT |
SQLite REAL |
SQLite BLOB |
SQLite TEXT |
SQLite TEXT but empty |
SQLite nearest affinity |
---|---|---|---|---|---|---|
bool | V | ∅ | T | V+ | ∅ | INT |
bit(n) | V n<=64 | ∅ | ∅ | ∅ | ∅ | INT |
bytea | ∅ | ∅ | ✔ | - | ? | BLOB |
date | V | V | T | V+ | NULL |
? |
float4 | V+ | ✔ | T | - | NULL |
REAL |
float8 | V+ | ✔ | T | - | NULL |
REAL |
geometry | ∅ | ∅ | V+ | ∅ | ∅ | BLOB |
geography | ∅ | ∅ | V+ | ∅ | ∅ | BLOB |
int2 | V+ | ? | T | - | NULL |
INT |
int4 | V+ | ? | T | - | NULL |
INT |
int8 | ✔ | ? | T | - | NULL |
INT |
json | ? | ? | T | V+ | ? | TEXT |
name | ? | ? | T | V | NULL |
TEXT |
numeric | V | V | T | ∅ | 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+ (only 16 bytes) |
V+ | ∅ | TEXT, BLOB |
varchar | ? | ? | T | ✔ | V | TEXT |
varbit(n) | V n<=64 | ∅ | V | ∅ | ∅ | INT |
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.
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 |
geometry | geometry |
geography | geography |
Note: in case of sqlite_fdw
compiling without GIS support,
GIS data types will be converted to bytea
.
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
In OS sqlite_fdw
works as executed code with permissions of user of PostgreSQL server. Usually it is postgres
OS user.
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 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 GRANT
s 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, remotesshfs
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 noforce_readonly
=true
option value. - You have
USAGE
rightGRANT
for theFOREIGN SERVER
. - The
FOREIGN TABLE
of SQLite table have noupdatable
=false
option value. - If the
FOREIGN TABLE
have noupdatable
option, ensureFOREIGN SERVER
have noupdatable
=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.
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()
-
sqlite_fdw_version(); Returns standard "version integer" as
major version * 10000 + minor version * 100 + bugfix
.
sqlite_fdw_version
--------------------
20400
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');
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:
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.
Once for a database you need, as PostgreSQL superuser.
CREATE EXTENSION sqlite_fdw;
Once for a foreign datasource you need, as PostgreSQL superuser. Please specify SQLite database path using database
option.
CREATE SERVER sqlite_server
FOREIGN DATA WRAPPER sqlite_fdw
OPTIONS (
database '/path/to/database'
);
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).
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.
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 FOREIGN SCHEMA someschema
FROM SERVER sqlite_server
INTO public;
Note: someschema
has no particular meaning and can be set to an arbitrary value.
For the table from previous examples
SELECT * FROM t1;
COPY
command for foreign tables is not supportedIMPORT
of generated column is not supportedINSERT
into a partitioned table which has foreign partitions is not supported. ErrorNot support partition insert
will display.TRUNCATE
insqlite_fdw
always delete data of both parent and child tables (no matter user inputsTRUNCATE table CASCADE
orTRUNCATE table RESTRICT
) if there are foreign-keys references withON DELETE CASCADE
clause.RETURNING
is not supported.
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.
Array support is experimental. Please be careful.
sqlite_fdw
only supportsARRAY
const, for example,ANY (ARRAY[1, 2, 3])
orANY ('{1, 2 ,3}')
.sqlite_fdw
does not supportARRAY
expression, for example,ANY (ARRAY[c1, 1, c1+0])
.- For
ANY(ARRAY)
clause,sqlite_fdw
deparses it usingIN
operator.
- For
sum
function of SQLite, output ofsum(bigint)
isinteger
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 ofbigint
, 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 andNaN
value treated in SQLite asNULL
. - SQLite support
+Infinity
and-Infinity
special values for IEEE 754-2008 numbers in SQL expressions with numeric context. This values can be readed with bothtext
andreal
affiniy, but can be writed to SQLite only withreal
affinity (as signed out of range value9.0e999
).
sqlite_fdw
boolean values support exists only forbool
columns in foreign table. SQLite documentation recommends to store boolean as value withinteger
affinity.NULL
isn't converted, 1 converted totrue
, all otherNOT NULL
values converted tofalse
. DuringSELECT ... WHERE condition_column
condition converted only tocondition_column
.sqlite_fdw
don't provides limited support of boolean values ifbool
column in foreign table mapped to SQLitetext
affinity.
sqlite_fdw
UUID values support exists only foruuid
columns in foreign table. SQLite documentation recommends to store UUID as value with bothblob
andtext
affinity.sqlite_fdw
can pushdown both reading and filtering bothtext
andblob
values.- Expected affinity of UUID value in SQLite table determined by
column_type
option of the column forINSERT
andUPDATE
commands. PostgreSQL supports bothblob
andtext
affinity.
sqlite_fdw
PostgreSQLbit
/varbit
values support based onint
SQLite data affinity, because there is no per bit operations for SQLiteblob
affinity data. Maximum SQLiteint
affinity value is 8 bytes length, hence maximumbit
/varbit
values length is 64 bits.sqlite_fdw
doesn't pushdown#
(XOR) operator because there is no equal SQLite operator.
Test directory have structure as following:
+---sql
| +---12.16
| | filename1.sql
| | filename2.sql
| |
| +---13.12
| | filename1.sql
| | filename2.sql
| |
.................
| \---15.4
| filename1.sql
| filename2.sql
|
\---expected
| +---12.16
| | filename1.out
| | filename2.out
| |
| +---13.12
| | filename1.out
| | filename2.out
| |
.................
| \---15.4
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.
Test data directory is /tmp/sqlite_fdw_test
. If you have /tmp
mounted as tmpfs
the tests will be up to 800% faster.
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 ()
{
}
}
- 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
- 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.