Does PostgreSQL manages tables inside tables?-CodePudding

I was assigned the task to create a simple Database Management System in a class so I looked up Postgres and noticed that the CLI tool (psql) has commands (\d and \l) that output information about the database and columns of a table in the form of tables like when you do a SELECT. So my question is If Postgres manages user tables inside system tables? and that way when you do \d or \l you are actually doing a SELECT on those system tables. This is just to understand if that would be a good way of managing tables in a database or not and just use regular data structures like lists.

CodePudding user response：

It does indeed. You can run psql with -E to see the queries it is using.

Then check the online manuals The items to search for are "system catalogs" and "INFORMATION_SCHEMA". The latter is a standard way of describing database schemas and should mostly work between different RDBMS.

CodePudding user response：

Yes, Postgres uses tables that it creates to manage the tables that you create.

There is an entire chapter in the documentation explaining. To quote:

The system catalogs are the place where a relational database management system stores schema metadata, such as information about tables and columns, and internal bookkeeping information. PostgreSQL's system catalogs are regular tables.

As mentioned in the other Answer, the SQL standard requires metadata be provided in some table structures as defined within the standard. These must be housed within a schema named exactly INFORMATION_SCHEMA. Postgres provides that schema and its prescribed tables, but implements them as a view on the actual system tables. See the chapter on INFORMATION_SCHEMA in Postgres documentation.

You can access the metadata, such as to get a list of all the tables you have defined, or get a list of all the columns you defined in a particular table. To do so, perform a query in SQL using SELECT like any other query.

For portability, meaning to write code that works in other database systems in addition to Postgres, query against INFORMATION_SCHEMA.
For additional details not required by the SQL standard, and for Postgres-specific info, query against the Postgres-specific system tables. Their names all start with pg_.