Horton
Menu

Thread

  1. Get table catalog from pg_indexes

    Igor Korot <ikorot01@gmail.com> — 2022-11-27T17:22:50Z

    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.

  2. Re: Get table catalog from pg_indexes

    Erik Wienhold <ewie@ewie.name> — 2022-11-27T17:59:35Z

    > On 27/11/2022 18:22 CET Igor Korot <ikorot01@gmail.com> 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

  3. Re: Get table catalog from pg_indexes

    Igor Korot <ikorot01@gmail.com> — 2022-11-27T18:42:11Z

    Hi, Erik,

On Sun, Nov 27, 2022 at 11:59 AM Erik Wienhold <ewie@ewie.name> wrote:
>
> > On 27/11/2022 18:22 CET Igor Korot <ikorot01@gmail.com> 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 <catalog>.pg_indexes...".
Will this work?

Thank you.

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

  4. Re: Get table catalog from pg_indexes

    Christophe Pettus <xof@thebuild.com> — 2022-11-27T18:44:22Z

    
> On Nov 27, 2022, at 10:42, Igor Korot <ikorot01@gmail.com> 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.

  5. Re: Get table catalog from pg_indexes

    Igor Korot <ikorot01@gmail.com> — 2022-11-27T18:53:18Z

    Hi, Christopher,

On Sun, Nov 27, 2022 at 12:45 PM Christophe Pettus <xof@thebuild.com> wrote:
>
>
>
> > On Nov 27, 2022, at 10:42, Igor Korot <ikorot01@gmail.com> 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.

  6. Re: Get table catalog from pg_indexes

    David G. Johnston <david.g.johnston@gmail.com> — 2022-11-27T18:54:47Z

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

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.

David J.

  7. Re: Get table catalog from pg_indexes

    Christophe Pettus <xof@thebuild.com> — 2022-11-27T18:55:16Z

    
> On Nov 27, 2022, at 10:53, Igor Korot <ikorot01@gmail.com> 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.

  8. Re: Get table catalog from pg_indexes

    Adrian Klaver <adrian.klaver@aklaver.com> — 2022-11-27T18:55:40Z

    On 11/27/22 10:42, Igor Korot wrote:
> Hi, Erik,
> 
> On Sun, Nov 27, 2022 at 11:59 AM Erik Wienhold <ewie@ewie.name> wrote:
>>
>>> On 27/11/2022 18:22 CET Igor Korot <ikorot01@gmail.com> 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 <catalog>.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

  9. Re: Get table catalog from pg_indexes

    David G. Johnston <david.g.johnston@gmail.com> — 2022-11-27T18:57:13Z

    On Sun, Nov 27, 2022 at 11:53 AM Igor Korot <ikorot01@gmail.com> wrote:

>
> On Sun, Nov 27, 2022 at 12:45 PM Christophe Pettus <xof@thebuild.com>
> wrote:
> >
>
> > In PostgreSQL terminology, a single PostgreSQL instance (the cluster)
> can have multiple databases.
>
> Just like any other DBMS.
>

The point was the terminology - write "database" and "cluster" instead of
"catalog" and whatever (if anything) the SQL Standard has in place for "a
group of databases".

David J.

  10. Re: Get table catalog from pg_indexes

    Ron Johnson <ronljohnsonjr@gmail.com> — 2022-11-27T21:31:44Z

    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




-- 
Angular momentum makes the world go 'round.

  11. Re: Get table catalog from pg_indexes

    Adrian Klaver <adrian.klaver@aklaver.com> — 2022-11-27T21:55:48Z

    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

  12. Re: Get table catalog from pg_indexes

    Igor Korot <ikorot01@gmail.com> — 2022-11-27T22:04:52Z

    Thx, Adrian.

On Sun, Nov 27, 2022 at 3:56 PM Adrian Klaver <adrian.klaver@aklaver.com> 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
>
>
>

  13. Re: Get table catalog from pg_indexes

    Ron Johnson <ronljohnsonjr@gmail.com> — 2022-11-27T22:10:19Z

    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.

  14. Re: Get table catalog from pg_indexes

    Igor Korot <ikorot01@gmail.com> — 2022-11-27T22:11:42Z

    David,

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

Thank you/

>
> David J.

  15. Re: Get table catalog from pg_indexes

    Igor Korot <ikorot01@gmail.com> — 2022-11-27T22:13:23Z

    Ron,

On Sun, Nov 27, 2022 at 4:10 PM Ron <ronljohnsonjr@gmail.com> 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.
>
>

  16. Re: Get table catalog from pg_indexes

    Adrian Klaver <adrian.klaver@aklaver.com> — 2022-11-27T22:20:55Z

    On 11/27/22 14:11, Igor Korot wrote:
> David,
> 
> On Sun, Nov 27, 2022 at 12:55 PM David G. Johnston
> <david.g.johnston@gmail.com> wrote:
>>
>> On Sun, Nov 27, 2022 at 11:42 AM Igor Korot <ikorot01@gmail.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.klaver@aklaver.com

  17. Re: Get table catalog from pg_indexes

    shammat@gmx.net — 2022-11-28T06:04:18Z

    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.

  18. Re: Get table catalog from pg_indexes

    Ron Johnson <ronljohnsonjr@gmail.com> — 2022-11-28T14:14:22Z

    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.