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!!
-
2wiki.postgresql.org/wiki/Foreign_data_wrappers#mysql_fdwAkina– Akina2020-01-13 11:07:31 +00:00Commented Jan 13, 2020 at 11:07
-
Try this link(stackoverflow.com/questions/8752477/…). It will be of helpdavidkihara– davidkihara2020-01-13 11:08:43 +00:00Commented Jan 13, 2020 at 11:08
-
Using a Postgres foreign data wrapperVesa Karjalainen– Vesa Karjalainen2020-01-13 11:12:31 +00:00Commented Jan 13, 2020 at 11:12
-
this link is helpful stackoverflow.com/questions/16246467/…Marwen Jaffel– Marwen Jaffel2020-01-13 11:12:59 +00:00Commented Jan 13, 2020 at 11:12
-
@davidkihara I think that's for SQL Server, not PostgreSQL or MySQLVesa Karjalainen– Vesa Karjalainen2020-01-13 11:17:04 +00:00Commented Jan 13, 2020 at 11:17
Add a comment
|
3 Answers
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.
Comments
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
Comments
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
