CREATE EXTENSION sqlite_fdw; CREATE SERVER sqlite_svr FOREIGN DATA WRAPPER sqlite_fdw OPTIONS (database '/tmp/sqlitefdw_test.db'); CREATE FOREIGN TABLE department(department_id int OPTIONS (key 'true'), department_name text) SERVER sqlite_svr; CREATE FOREIGN TABLE employee(emp_id int OPTIONS (key 'true'), emp_name text, emp_dept_id int) SERVER sqlite_svr; CREATE FOREIGN TABLE empdata(emp_id int OPTIONS (key 'true'), emp_dat bytea) SERVER sqlite_svr; CREATE FOREIGN TABLE numbers(a int OPTIONS (key 'true'), b varchar(255)) SERVER sqlite_svr; CREATE FOREIGN TABLE multiprimary(a int, b int OPTIONS (key 'true'), c int OPTIONS(key 'true')) SERVER sqlite_svr; SELECT * FROM department LIMIT 10; department_id | department_name ---------------+----------------- (0 rows) SELECT * FROM employee LIMIT 10; emp_id | emp_name | emp_dept_id --------+----------+------------- (0 rows) SELECT * FROM empdata LIMIT 10; emp_id | emp_dat --------+--------- (0 rows) INSERT INTO department VALUES(generate_series(1,100), 'dept - ' || generate_series(1,100)); INSERT INTO employee VALUES(generate_series(1,100), 'emp - ' || generate_series(1,100), generate_series(1,100)); INSERT INTO empdata VALUES(1, decode ('01234567', 'hex')); INSERT INTO numbers VALUES(1, 'One'); INSERT INTO numbers VALUES(2, 'Two'); INSERT INTO numbers VALUES(3, 'Three'); INSERT INTO numbers VALUES(4, 'Four'); INSERT INTO numbers VALUES(5, 'Five'); INSERT INTO numbers VALUES(6, 'Six'); INSERT INTO numbers VALUES(7, 'Seven'); INSERT INTO numbers VALUES(8, 'Eight'); INSERT INTO numbers VALUES(9, 'Nine'); SELECT count(*) FROM department; count ------- 100 (1 row) SELECT count(*) FROM employee; count ------- 100 (1 row) SELECT count(*) FROM empdata; count ------- 1 (1 row) EXPLAIN (COSTS FALSE) SELECT * FROM department d, employee e WHERE d.department_id = e.emp_dept_id LIMIT 10; QUERY PLAN -------------------------------------------------------- Limit -> Nested Loop Join Filter: (d.department_id = e.emp_dept_id) -> Foreign Scan on department d -> Materialize -> Foreign Scan on employee e (6 rows) EXPLAIN (COSTS FALSE) SELECT * FROM department d, employee e WHERE d.department_id IN (SELECT department_id FROM department) LIMIT 10; QUERY PLAN ------------------------------------------------------------------------- Limit -> Nested Loop -> Nested Loop Semi Join Join Filter: (d.department_id = department.department_id) -> Foreign Scan on department d -> Materialize -> Foreign Scan on department -> Materialize -> Foreign Scan on employee e (9 rows) SELECT * FROM department d, employee e WHERE d.department_id = e.emp_dept_id LIMIT 10; department_id | department_name | emp_id | emp_name | emp_dept_id ---------------+-----------------+--------+----------+------------- 1 | dept - 1 | 1 | emp - 1 | 1 2 | dept - 2 | 2 | emp - 2 | 2 3 | dept - 3 | 3 | emp - 3 | 3 4 | dept - 4 | 4 | emp - 4 | 4 5 | dept - 5 | 5 | emp - 5 | 5 6 | dept - 6 | 6 | emp - 6 | 6 7 | dept - 7 | 7 | emp - 7 | 7 8 | dept - 8 | 8 | emp - 8 | 8 9 | dept - 9 | 9 | emp - 9 | 9 10 | dept - 10 | 10 | emp - 10 | 10 (10 rows) SELECT * FROM department d, employee e WHERE d.department_id IN (SELECT department_id FROM department) LIMIT 10; department_id | department_name | emp_id | emp_name | emp_dept_id ---------------+-----------------+--------+----------+------------- 1 | dept - 1 | 1 | emp - 1 | 1 1 | dept - 1 | 2 | emp - 2 | 2 1 | dept - 1 | 3 | emp - 3 | 3 1 | dept - 1 | 4 | emp - 4 | 4 1 | dept - 1 | 5 | emp - 5 | 5 1 | dept - 1 | 6 | emp - 6 | 6 1 | dept - 1 | 7 | emp - 7 | 7 1 | dept - 1 | 8 | emp - 8 | 8 1 | dept - 1 | 9 | emp - 9 | 9 1 | dept - 1 | 10 | emp - 10 | 10 (10 rows) SELECT * FROM empdata; emp_id | emp_dat --------+------------ 1 | \x01234567 (1 row) DELETE FROM employee WHERE emp_id = 10; SELECT COUNT(*) FROM department LIMIT 10; count ------- 100 (1 row) SELECT COUNT(*) FROM employee WHERE emp_id = 10; count ------- 0 (1 row) UPDATE employee SET emp_name = 'UPDATEd emp' WHERE emp_id = 20; SELECT emp_id, emp_name FROM employee WHERE emp_name like 'UPDATEd emp'; emp_id | emp_name --------+------------- 20 | UPDATEd emp (1 row) UPDATE empdata SET emp_dat = decode ('0123', 'hex'); SELECT * FROM empdata; emp_id | emp_dat --------+--------- 1 | \x0123 (1 row) SELECT * FROM employee LIMIT 10; emp_id | emp_name | emp_dept_id --------+----------+------------- 1 | emp - 1 | 1 2 | emp - 2 | 2 3 | emp - 3 | 3 4 | emp - 4 | 4 5 | emp - 5 | 5 6 | emp - 6 | 6 7 | emp - 7 | 7 8 | emp - 8 | 8 9 | emp - 9 | 9 11 | emp - 11 | 11 (10 rows) SELECT * FROM employee WHERE emp_id IN (1); emp_id | emp_name | emp_dept_id --------+----------+------------- 1 | emp - 1 | 1 (1 row) SELECT * FROM employee WHERE emp_id IN (1,3,4,5); emp_id | emp_name | emp_dept_id --------+----------+------------- 1 | emp - 1 | 1 3 | emp - 3 | 3 4 | emp - 4 | 4 5 | emp - 5 | 5 (4 rows) SELECT * FROM employee WHERE emp_id IN (10000,1000); emp_id | emp_name | emp_dept_id --------+----------+------------- (0 rows) SELECT * FROM employee WHERE emp_id NOT IN (1) LIMIT 5; emp_id | emp_name | emp_dept_id --------+----------+------------- 2 | emp - 2 | 2 3 | emp - 3 | 3 4 | emp - 4 | 4 5 | emp - 5 | 5 6 | emp - 6 | 6 (5 rows) SELECT * FROM employee WHERE emp_id NOT IN (1,3,4,5) LIMIT 5; emp_id | emp_name | emp_dept_id --------+----------+------------- 2 | emp - 2 | 2 6 | emp - 6 | 6 7 | emp - 7 | 7 8 | emp - 8 | 8 9 | emp - 9 | 9 (5 rows) SELECT * FROM employee WHERE emp_id NOT IN (10000,1000) LIMIT 5; emp_id | emp_name | emp_dept_id --------+----------+------------- 1 | emp - 1 | 1 2 | emp - 2 | 2 3 | emp - 3 | 3 4 | emp - 4 | 4 5 | emp - 5 | 5 (5 rows) SELECT * FROM employee WHERE emp_id NOT IN (SELECT emp_id FROM employee WHERE emp_id IN (1,10)); emp_id | emp_name | emp_dept_id --------+-------------+------------- 2 | emp - 2 | 2 3 | emp - 3 | 3 4 | emp - 4 | 4 5 | emp - 5 | 5 6 | emp - 6 | 6 7 | emp - 7 | 7 8 | emp - 8 | 8 9 | emp - 9 | 9 11 | emp - 11 | 11 12 | emp - 12 | 12 13 | emp - 13 | 13 14 | emp - 14 | 14 15 | emp - 15 | 15 16 | emp - 16 | 16 17 | emp - 17 | 17 18 | emp - 18 | 18 19 | emp - 19 | 19 20 | UPDATEd emp | 20 21 | emp - 21 | 21 22 | emp - 22 | 22 23 | emp - 23 | 23 24 | emp - 24 | 24 25 | emp - 25 | 25 26 | emp - 26 | 26 27 | emp - 27 | 27 28 | emp - 28 | 28 29 | emp - 29 | 29 30 | emp - 30 | 30 31 | emp - 31 | 31 32 | emp - 32 | 32 33 | emp - 33 | 33 34 | emp - 34 | 34 35 | emp - 35 | 35 36 | emp - 36 | 36 37 | emp - 37 | 37 38 | emp - 38 | 38 39 | emp - 39 | 39 40 | emp - 40 | 40 41 | emp - 41 | 41 42 | emp - 42 | 42 43 | emp - 43 | 43 44 | emp - 44 | 44 45 | emp - 45 | 45 46 | emp - 46 | 46 47 | emp - 47 | 47 48 | emp - 48 | 48 49 | emp - 49 | 49 50 | emp - 50 | 50 51 | emp - 51 | 51 52 | emp - 52 | 52 53 | emp - 53 | 53 54 | emp - 54 | 54 55 | emp - 55 | 55 56 | emp - 56 | 56 57 | emp - 57 | 57 58 | emp - 58 | 58 59 | emp - 59 | 59 60 | emp - 60 | 60 61 | emp - 61 | 61 62 | emp - 62 | 62 63 | emp - 63 | 63 64 | emp - 64 | 64 65 | emp - 65 | 65 66 | emp - 66 | 66 67 | emp - 67 | 67 68 | emp - 68 | 68 69 | emp - 69 | 69 70 | emp - 70 | 70 71 | emp - 71 | 71 72 | emp - 72 | 72 73 | emp - 73 | 73 74 | emp - 74 | 74 75 | emp - 75 | 75 76 | emp - 76 | 76 77 | emp - 77 | 77 78 | emp - 78 | 78 79 | emp - 79 | 79 80 | emp - 80 | 80 81 | emp - 81 | 81 82 | emp - 82 | 82 83 | emp - 83 | 83 84 | emp - 84 | 84 85 | emp - 85 | 85 86 | emp - 86 | 86 87 | emp - 87 | 87 88 | emp - 88 | 88 89 | emp - 89 | 89 90 | emp - 90 | 90 91 | emp - 91 | 91 92 | emp - 92 | 92 93 | emp - 93 | 93 94 | emp - 94 | 94 95 | emp - 95 | 95 96 | emp - 96 | 96 97 | emp - 97 | 97 98 | emp - 98 | 98 99 | emp - 99 | 99 100 | emp - 100 | 100 (98 rows) SELECT * FROM employee WHERE emp_name NOT IN ('emp - 1', 'emp - 2') LIMIT 5; emp_id | emp_name | emp_dept_id --------+----------+------------- 3 | emp - 3 | 3 4 | emp - 4 | 4 5 | emp - 5 | 5 6 | emp - 6 | 6 7 | emp - 7 | 7 (5 rows) SELECT * FROM employee WHERE emp_name NOT IN ('emp - 10') LIMIT 5; emp_id | emp_name | emp_dept_id --------+----------+------------- 1 | emp - 1 | 1 2 | emp - 2 | 2 3 | emp - 3 | 3 4 | emp - 4 | 4 5 | emp - 5 | 5 (5 rows) SELECT * FROM numbers WHERE (CASE WHEN a % 2 = 0 THEN 1 WHEN a % 5 = 0 THEN 1 ELSE 0 END) = 1; a | b ---+------- 2 | Two 4 | Four 5 | Five 6 | Six 8 | Eight (5 rows) SELECT * FROM numbers WHERE (CASE b WHEN 'Two' THEN 1 WHEN 'Six' THEN 1 ELSE 0 END) = 1; a | b ---+----- 2 | Two 6 | Six (2 rows) create or replace function test_param_WHERE() returns void as $$ DECLARE n varchar; BEGIN FOR x IN 1..9 LOOP SELECT b INTO n from numbers WHERE a=x; raise notice 'Found number %', n; end loop; return; END $$ LANGUAGE plpgsql; SELECT test_param_WHERE(); NOTICE: Found number One NOTICE: Found number Two NOTICE: Found number Three NOTICE: Found number Four NOTICE: Found number Five NOTICE: Found number Six NOTICE: Found number Seven NOTICE: Found number Eight NOTICE: Found number Nine test_param_where ------------------ (1 row) SELECT b from numbers WHERE a=1; b ----- One (1 row) EXPLAIN(COSTS OFF) SELECT b from numbers WHERE a=1; QUERY PLAN ------------------------- Foreign Scan on numbers (1 row) SELECT a FROM numbers WHERE b = (SELECT NULL::text); a --- (0 rows) PREPARE stmt1 (int, int) AS SELECT * FROM numbers WHERE a=$1 or a=$2; EXECUTE stmt1(1,2); a | b ---+----- 1 | One 2 | Two (2 rows) EXECUTE stmt1(2,2); a | b ---+----- 2 | Two (1 row) EXECUTE stmt1(3,2); a | b ---+------- 2 | Two 3 | Three (2 rows) EXECUTE stmt1(4,2); a | b ---+------ 2 | Two 4 | Four (2 rows) -- generic plan EXECUTE stmt1(5,2); a | b ---+------ 2 | Two 5 | Five (2 rows) EXECUTE stmt1(6,2); a | b ---+----- 2 | Two 6 | Six (2 rows) EXECUTE stmt1(7,2); a | b ---+------- 2 | Two 7 | Seven (2 rows) DELETE FROM employee; DELETE FROM department; DELETE FROM empdata; DELETE FROM numbers; BEGIN; INSERT INTO numbers VALUES(1, 'One'); INSERT INTO numbers VALUES(2, 'Two'); COMMIT; SELECT * from numbers; a | b ---+----- 1 | One 2 | Two (2 rows) BEGIN; INSERT INTO numbers VALUES(3, 'Three'); ROLLBACK; SELECT * from numbers; a | b ---+----- 1 | One 2 | Two (2 rows) BEGIN; INSERT INTO numbers VALUES(4, 'Four'); SAVEPOINT my_savepoint; INSERT INTO numbers VALUES(5, 'Five'); ROLLBACK TO SAVEPOINT my_savepoint; INSERT INTO numbers VALUES(6, 'Six'); COMMIT; SELECT * from numbers; a | b ---+------ 1 | One 2 | Two 4 | Four 6 | Six (4 rows) -- duplicate key INSERT INTO numbers VALUES(1, 'One'); ERROR: failed to execute remote SQL: rc=19 UNIQUE constraint failed: numbers.b sql=INSERT INTO main."numbers"("a", "b") VALUES (?, ?) DELETE from numbers; BEGIN; INSERT INTO numbers VALUES(1, 'One'); INSERT INTO numbers VALUES(2, 'Two'); COMMIT; -- violate unique constraint UPDATE numbers SET b='Two' WHERE a = 1; ERROR: failed to execute remote SQL: rc=19 UNIQUE constraint failed: numbers.b sql=UPDATE main."numbers" SET "b" = ? WHERE a = ? SELECT * from numbers; a | b ---+----- 1 | One 2 | Two (2 rows) -- push down explain (costs off) SELECT * from numbers WHERE a = any(ARRAY[2,3,4,5]::int[]); QUERY PLAN ------------------------- Foreign Scan on numbers (1 row) -- (1,2,3) is pushed down explain (costs off) SELECT * from numbers WHERE a in (1,2,3) AND (1,2) < (a,5); QUERY PLAN ----------------------------------- Foreign Scan on numbers Filter: (ROW(1, 2) < ROW(a, 5)) (2 rows) -- not push down explain (costs off) SELECT * from numbers WHERE a in (a+2*a,5); QUERY PLAN ------------------------- Foreign Scan on numbers (1 row) -- not push down explain (costs off) SELECT * from numbers WHERE a = any(ARRAY[1,2,a]::int[]); QUERY PLAN -------------------------------------- Foreign Scan on numbers Filter: (a = ANY (ARRAY[1, 2, a])) (2 rows) SELECT * from numbers WHERE a = any(ARRAY[2,3,4,5]::int[]); a | b ---+----- 2 | Two (1 row) SELECT * from numbers WHERE a = any(ARRAY[1,2,a]::int[]); a | b ---+----- 1 | One 2 | Two (2 rows) INSERT INTO multiprimary VALUES(1,2,3); INSERT INTO multiprimary VALUES(1,2,4); UPDATE multiprimary SET b = 10 WHERE c = 3; SELECT * from multiprimary; a | b | c ---+----+--- 1 | 10 | 3 1 | 2 | 4 (2 rows) UPDATE multiprimary SET a = 10 WHERE a = 1; SELECT * from multiprimary; a | b | c ----+----+--- 10 | 10 | 3 10 | 2 | 4 (2 rows) UPDATE multiprimary SET a = 100, b=200, c=300 WHERE a=10 AND b=10; SELECT * from multiprimary; a | b | c -----+-----+----- 100 | 200 | 300 10 | 2 | 4 (2 rows) UPDATE multiprimary SET a = 1234; SELECT * from multiprimary; a | b | c ------+-----+----- 1234 | 200 | 300 1234 | 2 | 4 (2 rows) UPDATE multiprimary SET a = a+1, b=b+1 WHERE b=200 AND c=300; SELECT * from multiprimary; a | b | c ------+-----+----- 1235 | 201 | 300 1234 | 2 | 4 (2 rows) DELETE from multiprimary WHERE a = 1235; SELECT * from multiprimary; a | b | c ------+---+--- 1234 | 2 | 4 (1 row) DELETE from multiprimary WHERE b = 2; SELECT * from multiprimary; a | b | c ---+---+--- (0 rows) INSERT INTO multiprimary VALUES(1,2,3); INSERT INTO multiprimary VALUES(1,2,4); INSERT INTO multiprimary VALUES(1,10,20); INSERT INTO multiprimary VALUES(2,20,40); SELECT count(distinct a) from multiprimary; count ------- 2 (1 row) SELECT sum(b),max(b), min(b) from multiprimary; sum | max | min -----+-----+----- 34 | 20 | 2 (1 row) SELECT sum(b+5)+2 from multiprimary group by b/2 order by b/2; ?column? ---------- 16 17 27 (3 rows) SELECT sum(a) from multiprimary group by b having sum(a) > 0 order by sum(a); sum ----- 1 2 2 (3 rows) SELECT sum(a) A from multiprimary group by b having avg(abs(a)) > 0 AND sum(a) > 0 order by A; a --- 1 2 2 (3 rows) SELECT count(nullif(a, 1)) FROM multiprimary; count ------- 1 (1 row) SELECT * from multiprimary, numbers WHERE multiprimary.a=numbers.a; a | b | c | a | b ---+----+----+---+----- 1 | 2 | 3 | 1 | One 1 | 2 | 4 | 1 | One 1 | 10 | 20 | 1 | One 2 | 20 | 40 | 2 | Two (4 rows) INSERT INTO numbers VALUES(4, 'Four'); -- All where clauses are pushed down SELECT * FROM numbers WHERE abs(a) = 4 AND upper(b) = 'FOUR' AND lower(b) = 'four'; a | b ---+------ 4 | Four (1 row) EXPLAIN (verbose, costs off) SELECT b, length(b) FROM numbers WHERE abs(a) = 4 AND upper(b) = 'FOUR' AND lower(b) = 'four'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.numbers Output: b, length((b)::text) SQLite query: SELECT "b" FROM main."numbers" WHERE ((abs("a") = 4)) AND ((upper("b") = 'FOUR')) AND ((lower("b") = 'four')) (3 rows) -- Only "length(b) = 4" are pushed down SELECT b, length(b) FROM numbers WHERE length(b) = 4 AND power(1, a) != 0 AND length(reverse(b)) = 4; b | length ------+-------- Four | 4 (1 row) EXPLAIN (verbose, costs off) SELECT b, length(b) FROM numbers WHERE length(b) = 4 AND power(1, a) != 0 AND length(reverse(b)) = 4; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.numbers Output: b, length((b)::text) Filter: ((power('1'::double precision, (numbers.a)::double precision) <> '0'::double precision) AND (length(reverse((numbers.b)::text)) = 4)) SQLite query: SELECT "a", "b" FROM main."numbers" WHERE ((length("b") = 4)) (4 rows) INSERT INTO multiprimary (b,c) VALUES (99, 100); SELECT c FROM multiprimary WHERE COALESCE(a,b,c) = 99; c ----- 100 (1 row) DROP FUNCTION test_param_WHERE(); DROP FOREIGN TABLE numbers; DROP FOREIGN TABLE department; DROP FOREIGN TABLE employee; DROP FOREIGN TABLE empdata; DROP FOREIGN TABLE multiprimary; DROP SERVER sqlite_svr; DROP EXTENSION sqlite_fdw CASCADE;