Shows 'metadata' (data about data)

Postgresql has a hidden pg_catalog schema. This keeps track of:

  • System-designed components needed to keep the database server functioning
  • User-designed components (tables, views, indexes etc)
    • Concepts like Views and Indexes will be introduced later in this section

We can access this schema with normal SQL:

SELECT *
FROM pg_tables;

We do not need to include the pg_catalog schema name. Postgres knows where this is

Notice we can see:

  • All the tables in our sequel-mart-schema schema
  • Tables in the table-load-base schema (more to follow in this section)
  • Tables in the pg_catalog schema

All these tables can be viewed with a SELECT ... FROM

Examples of other tables in the pg_catalog schema are:

System Table Description
... ...
pg_database details of every database on the server
pg_tables details of tables on the server
pg_stats contents of every column in a table larger than 8KB
pg_stat_user_tables details of every user table inc. row counts and usage stats
... ...
pg_views views in the database, including system views
pg_indexes indexes on tables in the server
pg_user user accounts on the server
pg_locks locks taken in a transaction
1. pg_tables example 2. pg_stat_user_tables example