Re: Get table catalog from pg_indexes

On 11/27/22 14:11, Igor Korot wrote:
> David,
>
> On Sun, Nov 27, 2022 at 12:55 PM David G. Johnston
> <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>>
>> On Sun, Nov 27, 2022 at 11:42 AM Igor Korot <ikorot01(at)gmail(dot)com> wrote:
>>>
>>>
>>> It doesn't say anything about "current" DB - only the DB.
>>
>>
>> Yes, but you must be connected to some database in order to execute this command: "the database" refers to this database you are connected to.
>
> Yes, I am and I get that.
>
>>
>> The catalogs are not information_schema.
>>
>>>
>>> However, I think I can try "SELECT 1 FROM <catalog>.pg_indexes...".
>>> Will this work?
>>>
>>
>> What is that even supposed to mean? It also seems simple enough to just do that asking "will this work" is a waste of time. Just try it.
>
> Apparently it looks like this query fails to execute.
> I am connected to the "draft" database and running
>
> SELECT 1 FROM draft.pg_indexes;
>
> gives:
>
> [quote]
> ERROR:schema "draft" does not exist
> [/quote]

Because the layout is catalog.schema.table so:

SELECT 1 FROM draft.pg_catalog.pg_indexes;

Just because this works don't leap to assumption that:

<some_other_catalog>.pg_catalog.pg_indexes

will work. This only works with the current database name.

>
> Thank you/
>
>>
>> David J.
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com