Cross DB query
A question I always have, and I didn´t find anybody answering it. If it´s
possible
select * from MyDB.MySchema.MyTable;
And from user point of view ... all databases are accessible for the same
postgres instance, user just says connect to this or that database, why is
it not possible to do
select * from FirstDB.FirstSchema.FirstTable join SecondDB.SecondSchema.
SecondTable;
Everything I found was how to connect, using FDW or DBLink, but not why.
regards,
Marcos
On Wednesday, December 8, 2021, Marcos Pegoraro <marcos@f10.com.br> wrote:
A question I always have, and I didn´t find anybody answering it. If it´s
possible
select * from MyDB.MySchema.MyTable;
No, if you specify MyDB is must match the database you’ve chosen to log
into.
Everything I found was how to connect, using FDW or DBLink, but not why.
Because someone decades ago made that decision and all of the internals
rely upon it, making a change cost prohibitive. In short, we allow the
syntax because that standard says we should but its not very useful beyond
that.
I’m pretty sure I’ve seen this in the documentation but a quick glance
didn’t turn it up. Experimentation does prove that it works in this manner
though.
David J.