Get table catalog from pg_indexes

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]https://www.postgresql.org/docs/current/view-pg-indexes.html.

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

--
Erik

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.

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.

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.

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.

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.

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

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.

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.

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

Thx, Adrian.

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.

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/

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.

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

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.

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.