Joining postgres tables from different servers

Question

I want to extract data by joining tables from two different postgres hosted on different servers using java.

ResultSet resA = statement_A.executeQuery("select issue_id from Server_A.table_name");
ResultSet resB = statement_B.executeQuery("select issue_id from Server_B.table_name");

How can I get join query executed to get result set in this case ? Any pointers would be highly appreciated..

[Perhaps this SO Answer](https://stackoverflow.com/a/36904631/1018611) can help. — gtgaxiola, Aug 02 '18 at 19:55
If it is performance critical and the tables are big, use `postgres_fdw`. — Laurenz Albe, Aug 03 '18 at 06:37

NiVeR · Answer 1 · 2018-08-02T20:02:27.477

You can't do it in any automatic/magical way. What you can do is define a class that will have the union of properties of the two tables like:

public class JoinedResult{
   private int id;
   private int name;
   // all other common properties to both
   ...
   // properties exclusive to first table
   ...
   // properteis exclusive to second table
   ...
}

and construct a list of these object that will contain the joined result of both tables.

To make the actual construction you have a few options:

The first one and the easiest one (but not efficient) is to iterate both results with nested loops, and once the ids (or whatever key is used) match you should construct a JoinedResult.
The second one is a bit more complex but also more efficient:
- Iterate first result set and construct a map that will map the id to the object.
- Iterate second result set and construct a map that will map the id to the object.
- Run a loop over the keys of one of the maps you constructed and use that key to access the matching values in both maps, finally construct the joined object.

Joining postgres tables from different servers

1 Answers1