Re: Attaching database

Hi,

you can only run inquires now on information_schema for the database you
are connected to.

If you are looking/searching in pg_database though, you can information
globally.

Something changed recently (I am using Dbeaver); I was able to attach to
a single database and see all my databases; run queries, etc.

With the latest update, now you have to have a separate connection to
each database.

Navicat now does the same thing, so my thinking is they must have
changed the drivers.

Regards,

On 10/15/22 02:06, Julien Rouhaud wrote:
> 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(dot)table(at)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.
>
>
--
Alex Theodossis
alex(at)dossi(dot)info
347-514-5420