1

I am learning about PostgreSQL and am trying to understand basic concepts like schemas, catalogs and tables. I have been reading PostgreSQL: Up and Running, and to quote the book:

  • Schemas are ... the immediate next level of organization within each database, [they] organize your database into logical groups.
  • Catalogs are system schemas that store PostgreSQL builtin functions and metadata

I am currently reading about pg_database, and am confused whether it's a catalog or a table. From the documentation, it says "The catalog pg_database stores information about the available databases". But on pgAdmin4, it is listed as a table within the pg_catalog catalog.

enter image description here

In the book, there's a query that updates the datistemplate column as if it's a table.

UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'mydb';

So is pg_database a catalog or a table? Or is it (somehow) both?

1
  • System catalogs (or catalogues where I'm from) are another name for system tables - they store the system meta-data, i.e. the data about the data - types, lengths, constraints &c. Search for "catalog" here and read that sentence and the following one carefully. Check here as well - Codd distinguished between the system "catalog" and user "relations" ("tables") - that semantic distinction hasn't been retained, also, records are also known as "tuples". Answer, catalog = table (meta-data).
    – Vérace
    Dec 26, 2021 at 9:40

1 Answer 1

3

These are called System Catalogs. And Chapter 52. System Catalogs says that they are represented by regular tables in PostgreSQL:

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. You can drop and recreate the tables, add columns, insert and update values, and severely mess up your system that way. Normally, one should not change the system catalogs by hand, there are normally SQL commands to do that. (For example, CREATE DATABASE inserts a row into the pg_database catalog — and actually creates the database on disk.) There are some exceptions for particularly esoteric operations, but many of those have been made available as SQL commands over time, and so the need for direct manipulation of the system catalogs is ever decreasing.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.