Обсуждение: 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.
> 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
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
> 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 connectionis 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 smallnumber of global catalogs, like pg_database). So, if you are connected to database "a", you can't query the systemcatalogs 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:
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
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.
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 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 > > >
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 databaseyou 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 oftime. 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.
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. > >
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 databaseyou 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 oftime. 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.