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?
1 Answers
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.
Related