\c postgres postgres CREATE EXTENSION mysql_fdw; CREATE SERVER mysql_svr FOREIGN DATA WRAPPER mysql_fdw; CREATE USER MAPPING FOR postgres SERVER mysql_svr OPTIONS(username 'foo', password 'bar'); CREATE FOREIGN TABLE department(department_id int, department_name text) SERVER mysql_svr OPTIONS(dbname 'testdb', table_name 'department'); CREATE FOREIGN TABLE employee(emp_id int, emp_name text, emp_dept_id int) SERVER mysql_svr OPTIONS(dbname 'testdb', table_name 'employee'); CREATE FOREIGN TABLE empdata(emp_id int, emp_dat bytea) SERVER mysql_svr OPTIONS(dbname 'testdb', table_name 'empdata'); 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')); 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) DELETE FROM employee; DELETE FROM department; DELETE FROM empdata; DROP FOREIGN TABLE department; DROP FOREIGN TABLE employee; DROP FOREIGN TABLE empdata; DROP USER MAPPING FOR postgres SERVER mysql_svr; DROP SERVER mysql_svr; DROP EXTENSION mysql_fdw CASCADE;