--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..7.00 rows=7 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..58.00 rows=58 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..1.00 rows=1 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) --Testcase 48: INSERT INTO "type_DATE"(col) VALUES ('2021.02.23'); --Testcase 49: INSERT INTO "type_DATE"(col) VALUES ('2021/03/08'); --Testcase 50: INSERT INTO "type_DATE"(col) VALUES ('9999-12-30'); --Testcase 58: SELECT * FROM "type_DATE"; col ------------ 2021-02-23 2021-03-08 9999-12-30 (3 rows) --Testcase 51: INSERT INTO "type_TIME"(col) VALUES ('01:23:45'); --Testcase 52: INSERT INTO "type_TIME"(col) VALUES ('01:23:45.6789'); --Testcase 59: SELECT * FROM "type_TIME"; col --------------- 01:23:45 01:23:45.6789 (2 rows) --Testcase 60: EXPLAIN VERBOSE SELECT c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c17, c18, c19, c2, c21, c22, c23, c24 FROM alltypetest; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan on public.alltypetest (cost=10.00..57.00 rows=57 width=1400) Output: c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c17, c18, c19, c2, c21, c22, c23, c24 SQLite query: SELECT `c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8`, `c9`, `c10`, `c11`, `c12`, `c13`, `c14`, `c15`, `c17`, `c18`, `c19`, `c21`, `c22`, `c23`, `c24` FROM main."alltypetest" (3 rows) --Testcase 61: SELECT c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c17, c18, c19, c2, c21, c22, c23, c24 FROM alltypetest; c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c9 | c10 | c11 | c12 | c13 | c14 | c15 | c17 | c18 | c19 | c2 | c21 | c22 | c23 | c24 --------+-----+-------+--------+---------------+--------------+---------------+------------+----------------------+------------------------+---------------------+------------------+-------------------------------------------+-----------------------------+--------------------------+---------+--------------+-------------+-----+------+-------------+------------+--------------------- 583647 | 127 | 12767 | 388607 | 2036854775807 | 573709551615 | 2036854775807 | abcdefghij | abcdefghijjhgfjfuafh | Côte dIvoire Fijifoxju | Hôm nay tôi rất vui | I am happy today | 今日はとても幸せです 今日はとても幸せです | The quick brown fox jumps o | ABCDEFGHIJKLMNOPQRSTUVWX | 3.4e+18 | 1.79769e+108 | 1.79769e+88 | 127 | 1234 | 99999.99999 | 9999-12-31 | 9999-12-31 23:59:59 (1 row) --Testcase 53: CREATE FOREIGN TABLE type_JSON(col JSON OPTIONS (key 'true')) SERVER sqlite_svr OPTIONS (table 'type_TEXT'); --Testcase 54: INSERT INTO type_JSON(col) VALUES ('[1, 2, "foo", null]'); --Testcase 55: INSERT INTO type_JSON(col) VALUES ('{"bar": "baz", "balance": 7.77, "active": false}'::json); --Testcase 56 SELECT * FROM type_JSON; col -------------------------------------------------- [1, 2, "foo", null] {"bar": "baz", "balance": 7.77, "active": false} (2 rows) --Testcase 57 DELETE FROM type_JSON; --Testcase 47: DROP EXTENSION sqlite_fdw CASCADE; NOTICE: drop cascades to 39 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 limittest drop cascades to foreign table grem1_1 drop cascades to foreign table grem1_2 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 "type_DATE" drop cascades to foreign table "type_TIME" drop cascades to foreign table "BitT" drop cascades to foreign table notype drop cascades to foreign table typetest drop cascades to foreign table "type_TEXT" drop cascades to foreign table alltypetest drop cascades to foreign table shorty drop cascades to foreign table "A a" drop cascades to foreign table fts_table drop cascades to foreign table fts_table_data drop cascades to foreign table fts_table_idx drop cascades to foreign table fts_table_content drop cascades to foreign table fts_table_docsize drop cascades to foreign table fts_table_config drop cascades to foreign table type_json drop cascades to server sqlite2