--SET log_min_messages TO DEBUG1; --SET client_min_messages TO DEBUG1; --Testcase 44: CREATE EXTENSION sqlite_fdw; --Testcase 45: CREATE SERVER sqlite_svr FOREIGN DATA WRAPPER sqlite_fdw OPTIONS (database '/tmp/sqlitefdw_test.db'); --Testcase 46: CREATE SERVER sqlite2 FOREIGN DATA WRAPPER sqlite_fdw; IMPORT FOREIGN SCHEMA public FROM SERVER sqlite_svr INTO public; --Testcase 1: INSERT INTO "type_STRING"(col) VALUES ('string'); --Testcase 2: INSERT INTO "type_BOOLEAN"(col) VALUES (TRUE); --Testcase 3: INSERT INTO "type_BOOLEAN"(col) VALUES (FALSE); --Testcase 4: INSERT INTO "type_BYTE"(col) VALUES ('c'); --Testcase 5: INSERT INTO "type_SINT"(col) VALUES (32767); --Testcase 6: INSERT INTO "type_SINT"(col) VALUES (-32768); --Testcase 7: INSERT INTO "type_BINT"(col) VALUES (9223372036854775807); --Testcase 8: INSERT INTO "type_BINT"(col) VALUES (-9223372036854775808); --Testcase 9: INSERT INTO "type_INTEGER"(col) VALUES (9223372036854775807); --Testcase 10: INSERT INTO "type_FLOAT"(col) VALUES (3.1415); --Testcase 11: INSERT INTO "type_DOUBLE"(col) VALUES (3.14159265); --Testcase 12: INSERT INTO "type_TIMESTAMP" VALUES ('2017.11.06 12:34:56.789', '2017.11.06'); --Testcase 13: INSERT INTO "type_TIMESTAMP" VALUES ('2017.11.06 1:3:0', '2017.11.07'); --Testcase 14: INSERT INTO "type_BLOB"(col) VALUES (bytea('\xDEADBEEF')); --Testcase 15: INSERT INTO typetest VALUES(1,'a', 'b', 'c','2017.11.06 12:34:56.789', '2017.11.06 12:34:56.789' ) ; --Testcase 16: SELECT * FROM "type_STRING"; col -------- string (1 row) --Testcase 17: SELECT * FROM "type_BOOLEAN"; col ----- t f (2 rows) --Testcase 18: SELECT * FROM "type_BYTE"; col ----- c (1 row) --Testcase 19: SELECT * FROM "type_SINT"; col -------- 32767 -32768 (2 rows) --Testcase 20: SELECT * FROM "type_BINT"; col ---------------------- 9223372036854775807 -9223372036854775808 (2 rows) --Testcase 21: SELECT * FROM "type_INTEGER"; col --------------------- 9223372036854775807 (1 row) --Testcase 22: SELECT * FROM "type_FLOAT"; col -------- 3.1415 (1 row) --Testcase 23: SELECT * FROM "type_DOUBLE"; col ------------ 3.14159265 (1 row) set datestyle=ISO; --Testcase 24: SELECT * FROM "type_TIMESTAMP"; col | b -------------------------+--------------------- 2017-11-06 12:34:56.789 | 2017-11-06 00:00:00 2017-11-06 01:03:00 | 2017-11-07 00:00:00 (2 rows) --Testcase 25: SELECT * FROM "type_BLOB"; col ------------ \xdeadbeef (1 row) --Testcase 26: SELECT * FROM typetest; i | v | c | t | d | ti ---+---+------------+---+-------------------------+------------------------- 1 | a | b | c | 2017-11-06 12:34:56.789 | 2017-11-06 12:34:56.789 (1 row) --Testcase 27: insert into "type_STRING" values('TYPE'); --Testcase 28: insert into "type_STRING" values('type'); -- not pushdown --Testcase 29: SELECT *FROM "type_STRING" WHERE col like 'TYP%'; col ------ TYPE (1 row) --Testcase 30: EXPLAIN SELECT *FROM "type_STRING" WHERE col like 'TYP%'; QUERY PLAN ------------------------------------------------------------------------------- Foreign Scan on "type_STRING" (cost=10.00..1000000.00 rows=1000000 width=32) (1 row) -- pushdown --Testcase 31: SELECT *FROM "type_STRING" WHERE col ilike 'typ%'; col ------ TYPE type (2 rows) --Testcase 32: EXPLAIN SELECT *FROM "type_STRING" WHERE col ilike 'typ%'; QUERY PLAN ------------------------------------------------------------------------------- Foreign Scan on "type_STRING" (cost=10.00..1000000.00 rows=1000000 width=32) Filter: (col ~~* 'typ%'::text) (2 rows) --Testcase 33: SELECT *FROM "type_STRING" WHERE col ilike 'typ%' and col like 'TYPE'; col ------ TYPE (1 row) --Testcase 34: EXPLAIN SELECT *FROM "type_STRING" WHERE col ilike 'typ%' and col like 'TYPE'; QUERY PLAN ------------------------------------------------------------------------------- Foreign Scan on "type_STRING" (cost=10.00..1000000.00 rows=1000000 width=32) Filter: (col ~~* 'typ%'::text) (2 rows) --Testcase 35: SELECT * FROM "type_TIMESTAMP"; col | b -------------------------+--------------------- 2017-11-06 12:34:56.789 | 2017-11-06 00:00:00 2017-11-06 01:03:00 | 2017-11-07 00:00:00 (2 rows) --Testcase 36: EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM "type_TIMESTAMP" WHERE col > date ('2017.11.06 12:34:56.789') ; QUERY PLAN --------------------------------------------------------------------------------------------- Foreign Scan on public."type_TIMESTAMP" Output: col, b SQLite query: SELECT `col`, `b` FROM main."type_TIMESTAMP" WHERE ((`col` > '2017-11-06')) (3 rows) --Testcase 37: SELECT * FROM "type_TIMESTAMP" WHERE col > date ('2017.11.06 12:34:56.789') ; col | b -------------------------+--------------------- 2017-11-06 01:03:00 | 2017-11-07 00:00:00 2017-11-06 12:34:56.789 | 2017-11-06 00:00:00 (2 rows) --Testcase 38: EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM "type_TIMESTAMP" WHERE col::text > date ('2017.11.06 12:34:56.789')::text ; QUERY PLAN ----------------------------------------------------------------------- Foreign Scan on public."type_TIMESTAMP" Output: col, b Filter: (("type_TIMESTAMP".col)::text > ('2017-11-06'::date)::text) SQLite query: SELECT `col`, `b` FROM main."type_TIMESTAMP" (4 rows) --Testcase 39: SELECT * FROM "type_TIMESTAMP" WHERE col::text > date ('2017.11.06 12:34:56.789')::text ; col | b -------------------------+--------------------- 2017-11-06 12:34:56.789 | 2017-11-06 00:00:00 2017-11-06 01:03:00 | 2017-11-07 00:00:00 (2 rows) --Testcase 40: EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM "type_TIMESTAMP" WHERE col > b - interval '1 hour'; QUERY PLAN -------------------------------------------------------------------------------- Foreign Scan on public."type_TIMESTAMP" Output: col, b Filter: ("type_TIMESTAMP".col > ("type_TIMESTAMP".b - '@ 1 hour'::interval)) SQLite query: SELECT `col`, `b` FROM main."type_TIMESTAMP" (4 rows) --Testcase 41: SELECT * FROM "type_TIMESTAMP" WHERE col > b - interval '1 hour'; col | b -------------------------+--------------------- 2017-11-06 12:34:56.789 | 2017-11-06 00:00:00 (1 row) --Testcase 42: EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM "type_TIMESTAMP" WHERE col > b; QUERY PLAN ------------------------------------------------------------------------------------ Foreign Scan on public."type_TIMESTAMP" Output: col, b SQLite query: SELECT `col`, `b` FROM main."type_TIMESTAMP" WHERE ((`col` > `b`)) (3 rows) --Testcase 43: SELECT * FROM "type_TIMESTAMP" WHERE col > b; col | b -------------------------+--------------------- 2017-11-06 12:34:56.789 | 2017-11-06 00:00:00 (1 row) DROP EXTENSION sqlite_fdw CASCADE; NOTICE: drop cascades to 25 other objects DETAIL: drop cascades to server sqlite_svr drop cascades to foreign table department drop cascades to foreign table employee drop cascades to foreign table empdata drop cascades to foreign table numbers drop cascades to foreign table t drop cascades to foreign table multiprimary drop cascades to foreign table columntest drop cascades to foreign table noprimary drop cascades to foreign table "type_STRING" drop cascades to foreign table "type_BOOLEAN" drop cascades to foreign table "type_BYTE" drop cascades to foreign table "type_SINT" drop cascades to foreign table "type_BINT" drop cascades to foreign table "type_INTEGER" drop cascades to foreign table "type_FLOAT" drop cascades to foreign table "type_DOUBLE" drop cascades to foreign table "type_TIMESTAMP" drop cascades to foreign table "type_BLOB" drop cascades to foreign table "BitT" drop cascades to foreign table notype drop cascades to foreign table typetest drop cascades to foreign table shorty drop cascades to foreign table "A a" drop cascades to server sqlite2