Re: Get table catalog from pg_indexes

Re: Get table catalog from pg_indexes

On 11/27/22 10:42, Igor Korot wrote:
> Hi, Erik,
> 
> On Sun, Nov 27, 2022 at 11:59 AM Erik Wienhold  wrote:
>>
>>> On 27/11/2022 18:22 CET Igor Korot  wrote:
>>>
>>> Table pg_indexes does not contain a field for a catalog.
>>>
>>> So how do I get that?
>>>
>>> SELECT 1 FROM pg_indexes WHERE indexname = $1 AND tablename = $2 AND
>>> schemaname = $3
>>
>> Use SELECT current_database() if you need to know the catalog.
>> pg_indexes only covers the current database[1].
> 
>>From the lin yo referenced:
> 
> [quote]
> The view pg_indexes provides access to useful information about each
> index in the database.
> [/quote]
> 
> It doesn't say anything about "current" DB - only the DB.

https://www.postgresql.org/docs/current/catalogs-overview.html

"Most system catalogs are copied from the template database during 
database creation and are thereafter database-specific. A few catalogs 
are physically shared across all databases in a cluster; these are noted 
in the descriptions of the individual catalogs."


As in:

https://www.postgresql.org/docs/current/catalog-pg-database.html

"Unlike most system catalogs, pg_database is shared across all databases 
of a cluster: there is only one copy of pg_database per cluster, not one 
per database."

> 
> However, I think I can try "SELECT 1 FROM .pg_indexes...".
> Will this work?
> 
> Thank you.
> 
>>
>> [1] https://www.postgresql.org/docs/current/view-pg-indexes.html
>>
>> --
>> Erik
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Get table catalog from pg_indexes

> On 27/11/2022 18:22 CET Igor Korot  wrote:
>
> Table pg_indexes does not contain a field for a catalog.
>
> So how do I get that?
>
> SELECT 1 FROM pg_indexes WHERE indexname = $1 AND tablename = $2 AND
> schemaname = $3

Use SELECT current_database() if you need to know the catalog.
pg_indexes only covers the current database[1].

[1] https://www.postgresql.org/docs/current/view-pg-indexes.html

--
Erik

Re: Get table catalog from pg_indexes

On 11/28/22 00:04, Thomas Kellerer wrote:
> Igor Korot schrieb am 27.11.2022 um 23:13:
>>> I've never heard of a database referred to as a catalog.  (That's always
>>> been where a database's metadata -- i.e. the pg_catalog schema -- is stored.)
>> In the ODBC terminology the DB is usually referenced as catalog.
> JDBC uses the same term and the SQL standard as well.

That's good to know.

-- 
Angular momentum makes the world go 'round.

Re: Get table catalog from pg_indexes

Re: Get table catalog from pg_indexes

On 11/27/22 15:55, Adrian Klaver wrote:
> On 11/27/22 13:31, Ron wrote:
>> On 11/27/22 11:22, Igor Korot wrote:
>>> Hi, ALL,
>>> Table pg_indexes does not contain a field for a catalog.
>>>
>>> So how do I get that?
>>>
>>> SELECT 1 FROM pg_indexes WHERE indexname = $1 AND tablename = $2 AND
>>> schemaname = $3
>>
>> You did not look hard enough, or Google "postgresql pg_indexes".
>>
>> test=# \d pg_indexes
>>              View "pg_catalog.pg_indexes"
>>     Column   | Type | Collation | Nullable | Default
>> ------------+------+-----------+----------+---------
>> *schemaname* | name | |          |
>> *tablename*  | name | |          |
>> *indexname*  | name | |          |
>>   tablespace | name |           |          |
>>   indexdef   | text |           |          |
>>
>> https://www.postgresql.org/docs/current/view-pg-indexes.html
>
> What the OP was looking for a field in the above that was catalogname or 
> datname per:

I've never heard of a database referred to as a catalog.  (That's always 
been where a database's metadata -- i.e. the pg_catalog schema -- is stored.)

-- 
Angular momentum makes the world go 'round.

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
>  wrote:
>>
>> On Sun, Nov 27, 2022 at 11:42 AM Igor Korot  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 .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:

.pg_catalog.pg_indexes

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

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

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Get table catalog from pg_indexes

On 11/27/22 13:31, Ron wrote:
> On 11/27/22 11:22, Igor Korot wrote:
>> Hi, ALL,
>> Table pg_indexes does not contain a field for a catalog.
>>
>> So how do I get that?
>>
>> SELECT 1 FROM pg_indexes WHERE indexname = $1 AND tablename = $2 AND
>> schemaname = $3
> 
> You did not look hard enough, or Google "postgresql pg_indexes".
> 
> test=# \d pg_indexes
>              View "pg_catalog.pg_indexes"
>     Column   | Type | Collation | Nullable | Default
> ------------+------+-----------+----------+---------
> *schemaname* | name | |          |
> *tablename*  | name | |          |
> *indexname*  | name | |          |
>   tablespace | name |           |          |
>   indexdef   | text |           |          |
> 
> https://www.postgresql.org/docs/current/view-pg-indexes.html

What the OP was looking for a field in the above that was catalogname or 
datname per:

https://www.postgresql.org/docs/current/catalog-pg-database.html

Table "pg_catalog.pg_database"
     Column     |   Type    | Collation | Nullable | Default
---------------+-----------+-----------+----------+---------
  oid           | oid       |           | not null |
  datname       | name      |           | not null |
...

In other words to filter the pg_index results by database/catalog name. 
Since pg_index is scoped to the database you are in when you do the 
query that is not going to happen.

> 
> 
> 
> 
> -- 
> Angular momentum makes the world go 'round.

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Get table catalog from pg_indexes

> On Nov 27, 2022, at 10:42, Igor Korot  wrote:
> 
> It doesn't say anything about "current" DB - only the DB.

In PostgreSQL terminology, a single PostgreSQL instance (the cluster) can have multiple databases.  The database the connection is currently open to is the current database.

Re: Get table catalog from pg_indexes

Igor Korot schrieb am 27.11.2022 um 23:13:
>> I've never heard of a database referred to as a catalog.  (That's always
>> been where a database's metadata -- i.e. the pg_catalog schema -- is stored.)
>
> In the ODBC terminology the DB is usually referenced as catalog.

JDBC uses the same term and the SQL standard as well.

Re: Get table catalog from pg_indexes

Thx, Adrian.

On Sun, Nov 27, 2022 at 3:56 PM Adrian Klaver  wrote:
>
> On 11/27/22 13:31, Ron wrote:
> > On 11/27/22 11:22, Igor Korot wrote:
> >> Hi, ALL,
> >> Table pg_indexes does not contain a field for a catalog.
> >>
> >> So how do I get that?
> >>
> >> SELECT 1 FROM pg_indexes WHERE indexname = $1 AND tablename = $2 AND
> >> schemaname = $3
> >
> > You did not look hard enough, or Google "postgresql pg_indexes".
> >
> > test=# \d pg_indexes
> >              View "pg_catalog.pg_indexes"
> >     Column   | Type | Collation | Nullable | Default
> > ------------+------+-----------+----------+---------
> > *schemaname* | name | |          |
> > *tablename*  | name | |          |
> > *indexname*  | name | |          |
> >   tablespace | name |           |          |
> >   indexdef   | text |           |          |
> >
> > https://www.postgresql.org/docs/current/view-pg-indexes.html
>
> What the OP was looking for a field in the above that was catalogname or
> datname per:
>
> https://www.postgresql.org/docs/current/catalog-pg-database.html
>
> Table "pg_catalog.pg_database"
>      Column     |   Type    | Collation | Nullable | Default
> ---------------+-----------+-----------+----------+---------
>   oid           | oid       |           | not null |
>   datname       | name      |           | not null |
> ...
>
> In other words to filter the pg_index results by database/catalog name.
> Since pg_index is scoped to the database you are in when you do the
> query that is not going to happen.
>
> >
> >
> >
> >
> > --
> > Angular momentum makes the world go 'round.
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>
>

Re: Get table catalog from pg_indexes

Ron,

On Sun, Nov 27, 2022 at 4:10 PM Ron  wrote:
>
> On 11/27/22 15:55, Adrian Klaver wrote:
> > On 11/27/22 13:31, Ron wrote:
> >> On 11/27/22 11:22, Igor Korot wrote:
> >>> Hi, ALL,
> >>> Table pg_indexes does not contain a field for a catalog.
> >>>
> >>> So how do I get that?
> >>>
> >>> SELECT 1 FROM pg_indexes WHERE indexname = $1 AND tablename = $2 AND
> >>> schemaname = $3
> >>
> >> You did not look hard enough, or Google "postgresql pg_indexes".
> >>
> >> test=# \d pg_indexes
> >>              View "pg_catalog.pg_indexes"
> >>     Column   | Type | Collation | Nullable | Default
> >> ------------+------+-----------+----------+---------
> >> *schemaname* | name | |          |
> >> *tablename*  | name | |          |
> >> *indexname*  | name | |          |
> >>   tablespace | name |           |          |
> >>   indexdef   | text |           |          |
> >>
> >> https://www.postgresql.org/docs/current/view-pg-indexes.html
> >
> > What the OP was looking for a field in the above that was catalogname or
> > datname per:
>
> I've never heard of a database referred to as a catalog.  (That's always
> been where a database's metadata -- i.e. the pg_catalog schema -- is stored.)

In the ODBC terminology the DB is usually referenced as catalog.

Thank you.

>
> --
> Angular momentum makes the world go 'round.
>
>

Get table catalog from pg_indexes

Hi, ALL,
Table pg_indexes does not contain a field for a catalog.

So how do I get that?

SELECT 1 FROM pg_indexes WHERE indexname = $1 AND tablename = $2 AND
schemaname = $3

Thank you.

Re: Get table catalog from pg_indexes

Hi, Erik,

On Sun, Nov 27, 2022 at 11:59 AM Erik Wienhold  wrote:
>
> > On 27/11/2022 18:22 CET Igor Korot  wrote:
> >
> > Table pg_indexes does not contain a field for a catalog.
> >
> > So how do I get that?
> >
> > SELECT 1 FROM pg_indexes WHERE indexname = $1 AND tablename = $2 AND
> > schemaname = $3
>
> Use SELECT current_database() if you need to know the catalog.
> pg_indexes only covers the current database[1].

From the lin yo referenced:

[quote]
The view pg_indexes provides access to useful information about each
index in the database.
[/quote]

It doesn't say anything about "current" DB - only the DB.

However, I think I can try "SELECT 1 FROM .pg_indexes...".
Will this work?

Thank you.

>
> [1] https://www.postgresql.org/docs/current/view-pg-indexes.html
>
> --
> Erik

Re: Get table catalog from pg_indexes

David,

On Sun, Nov 27, 2022 at 12:55 PM David G. Johnston
 wrote:
>
> On Sun, Nov 27, 2022 at 11:42 AM Igor Korot  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 .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]

Thank you/

>
> David J.

Re: Get table catalog from pg_indexes

Hi, Christopher,

On Sun, Nov 27, 2022 at 12:45 PM Christophe Pettus  wrote:
>
>
>
> > On Nov 27, 2022, at 10:42, Igor Korot  wrote:
> >
> > It doesn't say anything about "current" DB - only the DB.
>
> In PostgreSQL terminology, a single PostgreSQL instance (the cluster) can have multiple databases.

Just like any other DBMS.

> The database the connection is currently open to is the current database.

Are you saying I can't run any query on other DB? Or connect to DB and
run select?

Thank you.

Re: Get table catalog from pg_indexes

Re: Get table catalog from pg_indexes

Re: Get table catalog from pg_indexes

> On Nov 27, 2022, at 10:53, Igor Korot  wrote:
> Are you saying I can't run any query on other DB? Or connect to DB and
> run select?

The query you run only looks at the system catalogs for the database you are connected to (with the exception of the small number of global catalogs, like pg_database).  So, if you are connected to database "a", you can't query the system catalogs of database "b" for what indexes are in them.