-- -- SELECT -- CREATE EXTENSION sqlite_fdw; CREATE SERVER sqlite_svr FOREIGN DATA WRAPPER sqlite_fdw OPTIONS (database '/tmp/sqlitefdw_test_core.db'); CREATE FOREIGN TABLE onek ( unique1 int4, unique2 int4, two int4, four int4, ten int4, twenty int4, hundred int4, thousand int4, twothousand int4, fivethous int4, tenthous int4, odd int4, even int4, stringu1 name, stringu2 name, string4 name ) SERVER sqlite_svr; CREATE FOREIGN TABLE onek2 ( unique1 int4, unique2 int4, two int4, four int4, ten int4, twenty int4, hundred int4, thousand int4, twothousand int4, fivethous int4, tenthous int4, odd int4, even int4, stringu1 name, stringu2 name, string4 name ) SERVER sqlite_svr; CREATE FOREIGN TABLE INT8_TBL( q1 int8 OPTIONS (key 'true'), q2 int8 OPTIONS (key 'true') ) SERVER sqlite_svr; CREATE FOREIGN TABLE person ( name text, age int4, location point ) SERVER sqlite_svr; -- btree index -- awk '{if($1<10){print;}else{next;}}' onek.data | sort +0n -1 -- SELECT * FROM onek WHERE onek.unique1 < 10 ORDER BY onek.unique1; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 0 | 998 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | AAAAAA | KMBAAA | OOOOxx 1 | 214 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | GIAAAA | OOOOxx 2 | 326 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | OMAAAA | OOOOxx 3 | 431 | 1 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 6 | 7 | DAAAAA | PQAAAA | VVVVxx 4 | 833 | 0 | 0 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 8 | 9 | EAAAAA | BGBAAA | HHHHxx 5 | 541 | 1 | 1 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 10 | 11 | FAAAAA | VUAAAA | HHHHxx 6 | 978 | 0 | 2 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 12 | 13 | GAAAAA | QLBAAA | OOOOxx 7 | 647 | 1 | 3 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 14 | 15 | HAAAAA | XYAAAA | VVVVxx 8 | 653 | 0 | 0 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 16 | 17 | IAAAAA | DZAAAA | HHHHxx 9 | 49 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 18 | 19 | JAAAAA | XBAAAA | HHHHxx (10 rows) -- -- awk '{if($1<20){print $1,$14;}else{next;}}' onek.data | sort +0nr -1 -- SELECT onek.unique1, onek.stringu1 FROM onek WHERE onek.unique1 < 20 ORDER BY unique1 using >; unique1 | stringu1 ---------+---------- 19 | TAAAAA 18 | SAAAAA 17 | RAAAAA 16 | QAAAAA 15 | PAAAAA 14 | OAAAAA 13 | NAAAAA 12 | MAAAAA 11 | LAAAAA 10 | KAAAAA 9 | JAAAAA 8 | IAAAAA 7 | HAAAAA 6 | GAAAAA 5 | FAAAAA 4 | EAAAAA 3 | DAAAAA 2 | CAAAAA 1 | BAAAAA 0 | AAAAAA (20 rows) -- -- awk '{if($1>980){print $1,$14;}else{next;}}' onek.data | sort +1d -2 -- SELECT onek.unique1, onek.stringu1 FROM onek WHERE onek.unique1 > 980 ORDER BY stringu1 using <; unique1 | stringu1 ---------+---------- 988 | AMAAAA 989 | BMAAAA 990 | CMAAAA 991 | DMAAAA 992 | EMAAAA 993 | FMAAAA 994 | GMAAAA 995 | HMAAAA 996 | IMAAAA 997 | JMAAAA 998 | KMAAAA 999 | LMAAAA 981 | TLAAAA 982 | ULAAAA 983 | VLAAAA 984 | WLAAAA 985 | XLAAAA 986 | YLAAAA 987 | ZLAAAA (19 rows) -- -- awk '{if($1>980){print $1,$16;}else{next;}}' onek.data | -- sort +1d -2 +0nr -1 -- SELECT onek.unique1, onek.string4 FROM onek WHERE onek.unique1 > 980 ORDER BY string4 using <, unique1 using >; unique1 | string4 ---------+--------- 999 | AAAAxx 995 | AAAAxx 983 | AAAAxx 982 | AAAAxx 981 | AAAAxx 998 | HHHHxx 997 | HHHHxx 993 | HHHHxx 990 | HHHHxx 986 | HHHHxx 996 | OOOOxx 991 | OOOOxx 988 | OOOOxx 987 | OOOOxx 985 | OOOOxx 994 | VVVVxx 992 | VVVVxx 989 | VVVVxx 984 | VVVVxx (19 rows) -- -- awk '{if($1>980){print $1,$16;}else{next;}}' onek.data | -- sort +1dr -2 +0n -1 -- SELECT onek.unique1, onek.string4 FROM onek WHERE onek.unique1 > 980 ORDER BY string4 using >, unique1 using <; unique1 | string4 ---------+--------- 984 | VVVVxx 989 | VVVVxx 992 | VVVVxx 994 | VVVVxx 985 | OOOOxx 987 | OOOOxx 988 | OOOOxx 991 | OOOOxx 996 | OOOOxx 986 | HHHHxx 990 | HHHHxx 993 | HHHHxx 997 | HHHHxx 998 | HHHHxx 981 | AAAAxx 982 | AAAAxx 983 | AAAAxx 995 | AAAAxx 999 | AAAAxx (19 rows) -- -- awk '{if($1<20){print $1,$16;}else{next;}}' onek.data | -- sort +0nr -1 +1d -2 -- SELECT onek.unique1, onek.string4 FROM onek WHERE onek.unique1 < 20 ORDER BY unique1 using >, string4 using <; unique1 | string4 ---------+--------- 19 | OOOOxx 18 | VVVVxx 17 | HHHHxx 16 | OOOOxx 15 | VVVVxx 14 | AAAAxx 13 | OOOOxx 12 | AAAAxx 11 | OOOOxx 10 | AAAAxx 9 | HHHHxx 8 | HHHHxx 7 | VVVVxx 6 | OOOOxx 5 | HHHHxx 4 | HHHHxx 3 | VVVVxx 2 | OOOOxx 1 | OOOOxx 0 | OOOOxx (20 rows) -- -- awk '{if($1<20){print $1,$16;}else{next;}}' onek.data | -- sort +0n -1 +1dr -2 -- SELECT onek.unique1, onek.string4 FROM onek WHERE onek.unique1 < 20 ORDER BY unique1 using <, string4 using >; unique1 | string4 ---------+--------- 0 | OOOOxx 1 | OOOOxx 2 | OOOOxx 3 | VVVVxx 4 | HHHHxx 5 | HHHHxx 6 | OOOOxx 7 | VVVVxx 8 | HHHHxx 9 | HHHHxx 10 | AAAAxx 11 | OOOOxx 12 | AAAAxx 13 | OOOOxx 14 | AAAAxx 15 | VVVVxx 16 | OOOOxx 17 | HHHHxx 18 | VVVVxx 19 | OOOOxx (20 rows) -- -- test partial btree indexes -- SET enable_seqscan TO off; SET enable_bitmapscan TO off; SET enable_sort TO off; -- -- awk '{if($1<10){print $0;}else{next;}}' onek.data | sort +0n -1 -- SELECT onek2.* FROM onek2 WHERE onek2.unique1 < 10; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 9 | 49 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 18 | 19 | JAAAAA | XBAAAA | HHHHxx 1 | 214 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | GIAAAA | OOOOxx 2 | 326 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | OMAAAA | OOOOxx 3 | 431 | 1 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 6 | 7 | DAAAAA | PQAAAA | VVVVxx 5 | 541 | 1 | 1 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 10 | 11 | FAAAAA | VUAAAA | HHHHxx 7 | 647 | 1 | 3 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 14 | 15 | HAAAAA | XYAAAA | VVVVxx 8 | 653 | 0 | 0 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 16 | 17 | IAAAAA | DZAAAA | HHHHxx 4 | 833 | 0 | 0 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 8 | 9 | EAAAAA | BGBAAA | HHHHxx 6 | 978 | 0 | 2 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 12 | 13 | GAAAAA | QLBAAA | OOOOxx 0 | 998 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | AAAAAA | KMBAAA | OOOOxx (10 rows) -- -- awk '{if($1<20){print $1,$14;}else{next;}}' onek.data | sort +0nr -1 -- SELECT onek2.unique1, onek2.stringu1 FROM onek2 WHERE onek2.unique1 < 20 ORDER BY unique1 using >; unique1 | stringu1 ---------+---------- 19 | TAAAAA 18 | SAAAAA 17 | RAAAAA 16 | QAAAAA 15 | PAAAAA 14 | OAAAAA 13 | NAAAAA 12 | MAAAAA 11 | LAAAAA 10 | KAAAAA 9 | JAAAAA 8 | IAAAAA 7 | HAAAAA 6 | GAAAAA 5 | FAAAAA 4 | EAAAAA 3 | DAAAAA 2 | CAAAAA 1 | BAAAAA 0 | AAAAAA (20 rows) -- -- awk '{if($1>980){print $1,$14;}else{next;}}' onek.data | sort +1d -2 -- SELECT onek2.unique1, onek2.stringu1 FROM onek2 WHERE onek2.unique1 > 980; unique1 | stringu1 ---------+---------- 997 | JMAAAA 995 | HMAAAA 999 | LMAAAA 983 | VLAAAA 989 | BMAAAA 986 | YLAAAA 996 | IMAAAA 982 | ULAAAA 992 | EMAAAA 990 | CMAAAA 991 | DMAAAA 984 | WLAAAA 981 | TLAAAA 998 | KMAAAA 993 | FMAAAA 994 | GMAAAA 988 | AMAAAA 987 | ZLAAAA 985 | XLAAAA (19 rows) RESET enable_seqscan; RESET enable_bitmapscan; RESET enable_sort; SELECT two, stringu1, ten, string4 INTO TABLE tmp FROM onek; -- -- awk '{print $1,$2;}' person.data | -- awk '{if(NF!=2){print $3,$2;}else{print;}}' - emp.data | -- awk '{if(NF!=2){print $3,$2;}else{print;}}' - student.data | -- awk 'BEGIN{FS=" ";}{if(NF!=2){print $4,$5;}else{print;}}' - stud_emp.data -- -- SELECT name, age FROM person*; ??? check if different SELECT p.name, p.age FROM person* p; name | age ---------+----- mike | 40 joe | 20 sally | 34 sandra | 19 alex | 30 sue | 50 denise | 24 sarah | 88 teresa | 38 nan | 28 leah | 68 wendy | 78 melissa | 28 joan | 18 mary | 8 jane | 58 liza | 38 jean | 28 jenifer | 38 juanita | 58 susan | 78 zena | 98 martie | 88 chris | 78 pat | 18 zola | 58 louise | 98 edna | 18 bertha | 88 sumi | 38 koko | 88 gina | 18 rean | 48 sharon | 78 paula | 68 julie | 68 belinda | 38 karen | 48 carina | 58 diane | 18 esther | 98 trudy | 88 fanny | 8 carmen | 78 lita | 25 pamela | 48 sandy | 38 trisha | 88 uma | 78 velma | 68 (50 rows) -- -- awk '{print $1,$2;}' person.data | -- awk '{if(NF!=2){print $3,$2;}else{print;}}' - emp.data | -- awk '{if(NF!=2){print $3,$2;}else{print;}}' - student.data | -- awk 'BEGIN{FS=" ";}{if(NF!=1){print $4,$5;}else{print;}}' - stud_emp.data | -- sort +1nr -2 -- SELECT p.name, p.age FROM person* p ORDER BY age using >, name; name | age ---------+----- esther | 98 louise | 98 zena | 98 bertha | 88 koko | 88 martie | 88 sarah | 88 trisha | 88 trudy | 88 carmen | 78 chris | 78 sharon | 78 susan | 78 uma | 78 wendy | 78 julie | 68 leah | 68 paula | 68 velma | 68 carina | 58 jane | 58 juanita | 58 zola | 58 sue | 50 karen | 48 pamela | 48 rean | 48 mike | 40 belinda | 38 jenifer | 38 liza | 38 sandy | 38 sumi | 38 teresa | 38 sally | 34 alex | 30 jean | 28 melissa | 28 nan | 28 lita | 25 denise | 24 joe | 20 sandra | 19 diane | 18 edna | 18 gina | 18 joan | 18 pat | 18 fanny | 8 mary | 8 (50 rows) -- -- Test VALUES lists -- select * from onek, (values(147, 'RFAAAA'), (931, 'VJAAAA')) as v (i, j) WHERE onek.unique1 = v.i and onek.stringu1 = v.j; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 | i | j ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------+-----+-------- 147 | 0 | 1 | 3 | 7 | 7 | 7 | 47 | 147 | 147 | 147 | 14 | 15 | RFAAAA | AAAAAA | AAAAxx | 147 | RFAAAA 931 | 1 | 1 | 3 | 1 | 11 | 1 | 31 | 131 | 431 | 931 | 2 | 3 | VJAAAA | BAAAAA | HHHHxx | 931 | VJAAAA (2 rows) -- a more complex case -- looks like we're coding lisp :-) select * from onek, (values ((select i from (values(10000), (2), (389), (1000), (2000), ((select 10029))) as foo(i) order by i asc limit 1))) bar (i) where onek.unique1 = bar.i; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 | i ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------+--- 2 | 326 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | OMAAAA | OOOOxx | 2 (1 row) -- try VALUES in a subquery select * from onek where (unique1,ten) in (values (1,1), (20,0), (99,9), (17,99)) order by unique1; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 1 | 214 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | GIAAAA | OOOOxx 20 | 306 | 0 | 0 | 0 | 0 | 0 | 20 | 20 | 20 | 20 | 0 | 1 | UAAAAA | ULAAAA | OOOOxx 99 | 101 | 1 | 3 | 9 | 19 | 9 | 99 | 99 | 99 | 99 | 18 | 19 | VDAAAA | XDAAAA | HHHHxx (3 rows) -- VALUES is also legal as a standalone query or a set-operation member VALUES (1,2), (3,4+4), (7,77.7) UNION ALL SELECT 2+2, 57 UNION ALL TABLE int8_tbl; column1 | column2 ------------------+------------------- 1 | 2 3 | 8 7 | 77.7 4 | 57 123 | 456 123 | 4567890123456789 4567890123456789 | -4567890123456789 4567890123456789 | 123 4567890123456789 | 4567890123456789 (9 rows) -- -- Test ORDER BY options -- CREATE FOREIGN TABLE foo (f1 int) SERVER sqlite_svr; INSERT INTO foo VALUES (42),(3),(10),(7),(null),(null),(1); SELECT * FROM foo ORDER BY f1; f1 ---- 1 3 7 10 42 (7 rows) SELECT * FROM foo ORDER BY f1 ASC; -- same thing f1 ---- 1 3 7 10 42 (7 rows) SELECT * FROM foo ORDER BY f1 NULLS FIRST; f1 ---- 1 3 7 10 42 (7 rows) SELECT * FROM foo ORDER BY f1 DESC; f1 ---- 42 10 7 3 1 (7 rows) SELECT * FROM foo ORDER BY f1 DESC NULLS LAST; f1 ---- 42 10 7 3 1 (7 rows) -- -- Test planning of some cases with partial indexes -- -- partial index is usable explain (costs off) select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; QUERY PLAN ----------------------- Foreign Scan on onek2 (1 row) select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 494 | 11 | 0 | 2 | 4 | 14 | 4 | 94 | 94 | 494 | 494 | 8 | 9 | ATAAAA | LAAAAA | VVVVxx (1 row) -- actually run the query with an analyze to use the partial index explain (costs off, analyze on, timing off, summary off) select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; QUERY PLAN ----------------------------------------------- Foreign Scan on onek2 (actual rows=1 loops=1) (1 row) explain (costs off) select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; QUERY PLAN ----------------------- Foreign Scan on onek2 (1 row) select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; unique2 --------- 11 (1 row) -- partial index predicate implies clause, so no need for retest explain (costs off) select * from onek2 where unique2 = 11 and stringu1 < 'B'; QUERY PLAN ----------------------- Foreign Scan on onek2 (1 row) select * from onek2 where unique2 = 11 and stringu1 < 'B'; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 494 | 11 | 0 | 2 | 4 | 14 | 4 | 94 | 94 | 494 | 494 | 8 | 9 | ATAAAA | LAAAAA | VVVVxx (1 row) explain (costs off) select unique2 from onek2 where unique2 = 11 and stringu1 < 'B'; QUERY PLAN ----------------------- Foreign Scan on onek2 (1 row) select unique2 from onek2 where unique2 = 11 and stringu1 < 'B'; unique2 --------- 11 (1 row) -- but if it's an update target, must retest anyway explain (costs off) select unique2 from onek2 where unique2 = 11 and stringu1 < 'B' for update; QUERY PLAN ----------------------------- LockRows -> Foreign Scan on onek2 (2 rows) select unique2 from onek2 where unique2 = 11 and stringu1 < 'B' for update; unique2 --------- 11 (1 row) -- partial index is not applicable explain (costs off) select unique2 from onek2 where unique2 = 11 and stringu1 < 'C'; QUERY PLAN ----------------------- Foreign Scan on onek2 (1 row) select unique2 from onek2 where unique2 = 11 and stringu1 < 'C'; unique2 --------- 11 (1 row) -- partial index implies clause, but bitmap scan must recheck predicate anyway SET enable_indexscan TO off; explain (costs off) select unique2 from onek2 where unique2 = 11 and stringu1 < 'B'; QUERY PLAN ----------------------- Foreign Scan on onek2 (1 row) select unique2 from onek2 where unique2 = 11 and stringu1 < 'B'; unique2 --------- 11 (1 row) RESET enable_indexscan; -- check multi-index cases too explain (costs off) select unique1, unique2 from onek2 where (unique2 = 11 or unique1 = 0) and stringu1 < 'B'; QUERY PLAN ----------------------- Foreign Scan on onek2 (1 row) select unique1, unique2 from onek2 where (unique2 = 11 or unique1 = 0) and stringu1 < 'B'; unique1 | unique2 ---------+--------- 494 | 11 0 | 998 (2 rows) explain (costs off) select unique1, unique2 from onek2 where (unique2 = 11 and stringu1 < 'B') or unique1 = 0; QUERY PLAN ----------------------- Foreign Scan on onek2 (1 row) select unique1, unique2 from onek2 where (unique2 = 11 and stringu1 < 'B') or unique1 = 0; unique1 | unique2 ---------+--------- 494 | 11 0 | 998 (2 rows) DROP FOREIGN TABLE onek; DROP FOREIGN TABLE onek2; DROP FOREIGN TABLE int8_tbl; DROP FOREIGN TABLE person; DROP FOREIGN TABLE foo; DROP SERVER sqlite_svr; DROP EXTENSION sqlite_fdw CASCADE;