JIYIK CN >

Let's start with a simple question. INFORMATION_SCHEMAWhat exactly is it?

INFORMATION_SCHEMAProvides us with information about the objects defined in our database. It contains a set VIEWSof objects that store QUERYthe database objects.

The following query acts like a VIEWin that it returns a YOUR_TABLElogical containing rows TABLE.

Select * from [your_table]

INFORMATION_SCHEMAis pre-existing, which means that the database user can access this table and all privileges, incl., when needed DROP.

This SCHEMAcontains various database objects, so if you want to access a specific object, it is best to write the name of the object, for example TABLES, to retrieve all tables.

Now let's go ahead and see how we can use it.

INFORMATION_SCHEMABasic SELECTquery to search the table in PostgreSQL

To get SCHEMAall the tables in this , a very simple query is to write something like this.

select * from information_schema.tables

This will return a table like this.

Output:

Here you can see all the tables in the database and TYPEtheir

However, there is one confusing aspect to this output. If you scroll down OUTPUTa bit, you'll notice this.

Output:

Here you will see different tables TABLE_SCHEMAset to INFORMATION_SCHEMA. But we are not calling INFORMATION_SCHEMA.TABLES, so what is this?

Let us explain. TABLE_SCHEMATell us SCHEMAcontains the table.

When we call INFORMATION_SCHEMA.TABLES, it returns all objects defined by the database rules in its document. Therefore, it also includes PG_CATALOGand PUBLICtables.

But tables that TABLE_SCHEMAhave set to tend to follow something called , which means they can be viewed on other different DBMS systems. You can see this by searching for on Google .INFORMATION_SCHEMASQL STANDARDISO/IEC 9075SQL 标准

In contrast, PG_CATALOGthere are only PostgreSQL-specific tables; therefore, they are included in this domain.

As a side note, INFORMATION_SCHEMAyou might also prefer to SYSTEMexport these tables as compliant SQL STANDARDmetadata.

Query modification INFORMATION_SCHEMAin PostgreSQLSELECT

To get the tables individually from the range of tables returned by the above query INFORMATION_SCHEMA, we use:

select * from information_schema.tables where table_schema = 'information_schema'

Or, if you want to display a different set of tables, you can TABLE_SCHEMAchange to PUBLICor PG_CATALOG.

Running the same query in PSQLreturns:

Output:

PostgreSQL INFORMATION_SCHEMAList (Job Update) PSQLStatement

To PSQLview it in the console INFORMATION_SCHEMA, you can issue the following statement:

postgres=# \dt information_schema.*

This will return INFORMATION_SCHEMAall objects in . DTServes as a shorthand for listing a table.

Returns the table in a PostgreSQL database\z

Another very commonly used method might include \z. This is incorrectly written as '/z', which can lead to errors.

The statement is as follows.

postgres-# \z

This basically returns the table made by the user. Additionally, you can use:

postgres-# \dn information_schema

This tells SCHEMAus OWNER.

\zModify to get all tables in PostgreSQL

An alternative to the first solution is to use:

postgres-# \dt *.*

This will return all tables as before.

We hope you've seen some of the different ways we can display tables for our users INFORMATION_SCHEMA. We always do our best to explore possible solutions to problems.

Today we have covered INFORMATION_SCHEMAthe display of and the different commands and tables that may be used.