Is there any way join tables of MySql and PostgreSQL?

Question

I have a table in MySql in one server and a table in PostgreSQL in another server. I want use use JOIN operation with those tables. Is there any way to join the columns? If not, is there any way to print the rows in same order? Please help!!

http://wiki.postgresql.org/wiki/Foreign_data_wrappers#mysql_fdw — Akina, Jan 13 '20 at 11:07
Try this link(https://stackoverflow.com/questions/8752477/joining-tables-from-different-servers). It will be of help — davidkihara, Jan 13 '20 at 11:08
[Using a Postgres foreign data wrapper](https://stackoverflow.com/questions/24683035/setup-mysql-foreign-data-wrapper-in-postgresql) — Vesa Karjalainen, Jan 13 '20 at 11:12
this link is helpful https://stackoverflow.com/questions/16246467/performing-a-join-across-multiple-heterogenous-databases-e-g-postgresql-and-mys — Marwen Jaffel, Jan 13 '20 at 11:12
@davidkihara I think that's for SQL Server, not PostgreSQL or MySQL — Vesa Karjalainen, Jan 13 '20 at 11:17

score 4 · Answer 1 · answered Jan 13 '20 at 17:22

4

Use mysql_fdw to define the MySQL table as a foreign table in PostgreSQL. Then you can join it with the PostgreSQL table in PostgreSQL.

answered Jan 13 '20 at 17:22

Laurenz Albe

209,280
17
206
263

score 2 · Answer 2 · answered Mar 18 '22 at 10:53

You can use Materialize to achieve this.

Here is a sample demo project that you can run to see this in action:

You can find the code for the demo project and how to run it on GitHub here:

https://github.com/bobbyiliev/materialize-tutorials/tree/main/mz-join-mysql-and-postgresql

score 1 · Answer 3 · answered Feb 05 '20 at 11:03

Hope this reference helps.

Yes, it is possible to work with multiple databases at the same time but you're looking in the wrong place. psycopg2 is just a library that simplifies accessing and manipulating data coming out of PostgreSQL but it doesn't go far beyond what you can do with psql. What you're looking to do you can solve on the database level by using Foreign Data Wrappers.

This does become more complicated in your schema definition but brings remote tables from host some.other.server database remote_db to appear as though they live on localhost in database local_db.... More: https://dba.stackexchange.com/a/120682/197899

Is there any way join tables of MySql and PostgreSQL?

3 Answers3