querying for data from two tables in different databases

Question

Initially I thought this would be a stupid question, but now I am inspired by the following question.

Background: I have a lot of data in MySQL, but MySQL's spatial support is terrible. Ideally I would like to migrate everything to Postgres, but converting from MySQL to Postgres is a massive ball of hurt (I've already wasted close to a week struggling with it). Now I am thinking, if only I could maintain only the spatial portion in Pg, do the spatial queries in Pg, then use those row ids to query non-spatial data from MySQL.

I am a Perl DBI person. My question is thus -- can I create a single database handle that actually allows querying by JOINing a table from Pg with a table from MySQL, assuming they have a common id column?

score 2 · Answer 1 · edited May 23 '17 at 11:55

2

No, you will need to query both separately and combine the data at the application layer. See a more informed answer here:

How to create linked server MySQL

edited May 23 '17 at 11:55

Community

1
1

answered Dec 08 '11 at 04:05

jmacinnes

1,589
1
11
21

score 1 · Answer 2 · edited Dec 08 '11 at 04:59

1

No, I don't think you could do it that way. You would have to query the data separately and combine the results in your code. I believe there are no REAL RDB's that can do what you want.

edited Dec 08 '11 at 04:59

xpda

15,585
8
51
82

answered Dec 08 '11 at 04:09

Mingliang

69
1
6

querying for data from two tables in different databases

2 Answers2