Menu
What is the difference between information_schema.tables and pg_tables tables in Postgres database? pg_tables has less number of tables in the output but what are the results these 2 tables at system perspective?
508
INFORMATION_SCHEMA provides access to database metadata, information about the MySQL server such as the name of a database or table, the data type of a column, or access privileges. Other terms that are sometimes used for this information are data dictionary and system catalog.
The information schema is a built-in schema that's common to every PostgreSQL database. You can run SQL queries against tables in the information_schema to fetch schema metadata for a database. For example, the following query fetches the names of all user-defined tables in a database: SELECT. table_name.
The INFORMATION_SCHEMA database is an ANSI standard set of views we can find in SQL Server, but also MySQL. Other database systems also have either exactly such or similar database implemented. It provides the read-only access to details related to databases and their objects (tables, constraints, procedures, views…)
The information_schema database (often called I_S for brevity) is a virtual database that contains informative tables. These tables can be divided into several groups: Metadata tables: Tables such as SCHEMATA , TABLES , and COLUMNS contain information about the structure of databases, tables, columns, and so on.
The views in the INFORMATION_SCHEMA are defined by the SQL standard and display information that is required by that. So they can't display any Postgres specific information that doesn't go along with the rules of the SQL standard. So queries using that are likely to work on other DBMS products as well that support INFORMATION_SCHEMA Not all products implement it 100% correct though. Postgres also has some areas where it deviates from the specification of the INFORMATION_SCHEMA. But the similarities are close enough that it's really easy to port and use such a query with a different database.
All system tables and views in the pg_catalog schema (including pg_tables) are completely Postgres specific. Queries using those will never run on other DBMS products. The INFORMATION_SCHEMA views use those system views and tables to collect and present the metadata as required by the SQL standard.
198
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
