I use GoLang. I have two different DB servers:

1. Postgresql
2. MS SQL.

For both of them I make a connection each:

db1, err := sql.Open("postgres", psqlconn)
db2, err := sql.Open("sqlserver", u.String())

The connections are working fine.

Now I want to connect data of the first DB with the second DB: using JOIN. Example:

rows, err := db1.Query('select *
    from db1.dbname1.tabel
    left join db2.dbname2.tabel on db2.dbname2.tabel.id = db1.dbname1.tabel.id;')

But this does not work :( How can I JOIN the two tables together? I am not getting anywhere. Or is there another way to do this?

Thanks for your support!! Matthias

CodePudding user response：

ORMs like gorm are basically an object relational layer, that provide some APIs that can translate your language level entities to SQL queries and vice versa. They basically translate your query in a single SQL query, now your postgresql database receives a query, which is a join of table a (which happens to exist) and another table b which is in another database! Joining is done in database level, not the ORM level. So you cannot achieve this.

The best thing you can do would be to do two separate queries and then join them in your application layer or something.

CodePudding user response：

You should be able to link the databases together using a foreign data wrapper from PostgreSQL to MSSQLSERVER (https://github.com/tds-fdw/tds_fdw) or in the reverse direction using whatever it is that MSSQLSERVER call their equivalent feature. Then you would send the combined query to just one of the databases for it to execute.

Or you could fetch both datasets into GO and join them there, using hash tables and for loops or something. If the datasets are too large to fit into memory, that would be pretty annoying.