I have two PostgreSQL databases with data that I would like to combine in one table for displaying on the screen in a web application. I’ve done some searching through Google and this forum to see if something like this has been answered in the past. All I’ve seen so far is doing JOINs, but these two tables are in two different databases so a JOIN won’t work.
There are only about 15 records total that will get pulled from the tables for each request. Do any of you have recommendations on how I can go about doing this?
[quote=425826:@Robert Litchfield]I have two PostgreSQL databases […]
All I’ve seen so far is doing JOINs, but these two tables are in two different databases so a JOIN won’t work.
[/quote]
Sure, this is possible, even with optimal performance using the postgres_fdw or the legacy dblink extension. An alternative way would be to copy both data sets to a temporary in-memory SQLiteDatabase and do the join there. But to display 15 records in a listbox, why do you need them to be in the same recordset at all?
@Tobias Bussmann Combining the recordsets was my initial thought…if there is a more efficient way to combine them, so they can be displayed in a particular order (by time in this case) then I’m open to anything.
Create an in-memory sqlite DB, create a table 1 and table2. Insert data into table1 from postgresql-1, Insert data into table2 from postgresql-2, do joins or whatever needed; close the in-memory database.
This might not be good for large record counts, but you could query as many databases / tables as you’d like and then add the data to a Dictionary. The tables could be very different as you can massage the data as you add it to the Dictionary. This would also work for any data source like xml, json, or any api too.
This was one direction I considered going. The pair would have been time:event. That idea failed when I realized there might be multiple events at one time so I couldn’t figure out how to make one time = two different events.
To provide context…this is for a new digital display for one of our early education galleries. The picture includes data from one of the databases, I need to add additional information from a second one (in the blue and purple time boxes).
Thanks for that. Could you have a Dictionary of Timestamps that contain a Dictionary Array of Events?
Something like:
2019-02-27 10am ( Baby Flying , Baby Swiming )
2019-02-27 1pm ( A Thing )
2019-02-27 3pm ( Toddler Tantrum )
Then you could loop thru the Dictionary of Timestamps and know how many events it as based on the Ubound of the Dictionary Array of Events to then draw what you need. Since “2019-02-27 10am” has two Events, you’d know to draw them at half width.
