Hi, On Fri, Oct 14, 2022 at 11:16:44PM -0500, Igor Korot wrote: > > Sorry for resurrecting this old thread... > If an attaching the DB creates new connection which will be cmpletely > independent - how the INFORMATION_SCHEMA.table@table_catalog > field is handled. > > Lets say I open connection to the DB (finance) and then attached another DB > (finance_2021). > > So, when I call SELECT table_schema, table_name FROM INFORMATION_SCHEMA.table > I will get all tables from (finance) DB only. > And to get all tables from (finance_2021) I need to make this catalog current > and reissue the query. > > Am I right? No. In postgres, databases are completely disjoint containers and once you have a connection on a given database it will stay on that database, there's no way around that. Using postgres_fdw allows you to create a local table that will point to another table, possibly on another database or even another server, but it will still be a (foreign) table, that has to be created in the current database in the schema of your choice. Depending on your use case, maybe what you could do is create a finance_2021 schema, and create all the foreign tables you need in that schema pointing to the finance_2021 database. Any table existing in both "finance" and "finance_2021" will then be visible in information_schema.tables, with a different table_schema. If you have multiple schemas in each database, then find a way to make it work, maybe adding a _2021 suffix on each schema or something like that. You can then maybe use the search_path (see https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH) to use by default one of the set of tables rather than the other. But in any case, it's only a workaround that has to be implemented on your client, as you will always be connected on the same database, and see both set of object in information_schema.
