Postgresql internal (server)

this thing explain the internal of pgsql system (not the source code I mean)

difference between catalog vs schema

key point:

• schema is folder like, grouping table together, can be created using

  CREATE SCHEMA my_catalog;
  CREATE TABLE my_catalog.metadata_table (...);
  

• catalog is system schema, can't be created manually, part of internal pgsql system

• view is a resulted table from XYZ queries, example

  CREATE VIEW active_users AS 
  	SELECT id, name, 
  	FROM
  		users 
  	WHERE
  		active = true;
  

  then query it

  SELECT * FROM active_users;
  

pg_catalog inside

this is some stuff inside of pg catalog, you can do that using \dt pg_catalog.*

• pg_aggregate: Stores information about aggregate functions (like SUM, AVG, etc.).
• pg_am: Lists access methods for indexes (e.g., btree, hash).
• pg_amop: Defines operators used in access methods.
• pg_amproc: Defines support functions used in access methods.
• pg_attrdef: Stores default values for columns.
• pg_attribute: Contains column definitions for all tables.
• pg_auth_members: Shows role memberships (who is a member of what).
• pg_authid: Stores user/role definitions (superuser access required).
• pg_cast: Contains rules for casting between data types.
• pg_class: Contains all table-like objects (tables, views, indexes, sequences, etc.).
• pg_collation: Defines collations (rules for string comparison).
• pg_constraint: Stores constraints like PRIMARY KEY, UNIQUE, CHECK, and FOREIGN KEY.
• pg_conversion: Defines character set conversions.
• pg_database: Stores information about each database in the cluster.
• pg_db_role_setting: Stores per-user/per-database configuration settings (GUCs).
• pg_default_acl: Defines default privileges for newly created objects.
• pg_depend: Stores dependency relationships between database objects.
• pg_description: Stores comments/descriptions on database objects.
• pg_enum: Stores values for ENUM data types.
• pg_event_trigger: Stores event trigger definitions (triggers on DDL commands).
• pg_extension: Tracks installed extensions (like uuid-ossp, pgcrypto, etc.).
• pg_foreign_data_wrapper: Stores definitions of foreign data wrappers (FDW).
• pg_foreign_server: Stores foreign servers used by FDWs.
• pg_foreign_table: Stores metadata for foreign tables.
• pg_index: Contains metadata about indexes (e.g., indexed columns).
• pg_inherits: Stores table inheritance relationships.
• pg_init_privs: Records original privileges on built-in objects.
• pg_language: Stores information about supported procedural languages.
• pg_largeobject: Stores the actual data of large objects (blobs).
• pg_largeobject_metadata: Stores metadata about large objects.
• pg_namespace: Lists all schemas in the database (IMPORTANT)
• pg_opclass: Stores index operator classes (how a datatype is indexed).
• pg_operator: Stores SQL operators (like =, <, +, etc.).
• pg_opfamily: Groups related operator classes.
• pg_parameter_acl: Stores access control for configuration parameters (PostgreSQL 16+).
• pg_partitioned_table: Stores metadata for partitioned tables.
• pg_policy: Stores row-level security policies.
• pg_proc: Contains all function and procedure definitions.
• pg_publication: Stores logical replication publications.
• pg_publication_namespace: Links publications to schemas.
• pg_publication_rel: Links publications to individual tables.
• pg_range: Stores definitions of range types (e.g., int4range).
• pg_replication_origin: Tracks origins for logical replication.
• pg_rewrite: Stores query rewrite rules (used in views, rules).
• pg_seclabel: Stores security labels for database objects.
• pg_sequence: Contains metadata for sequence generators.
• pg_shdepend: Tracks dependencies involving shared objects (like roles, databases).
• pg_shdescription: Stores comments on shared objects.
• pg_shseclabel: Stores security labels on shared objects.
• pg_statistic: Stores planner statistics for columns.
• pg_statistic_ext: Stores extended planner statistics (multi-column, NDV, etc.).
• pg_statistic_ext_data: Contains actual values for extended statistics.
• pg_subscription: Defines logical replication subscriptions.
• pg_subscription_rel: Lists tables included in subscriptions.
• pg_tablespace: Lists all tablespaces (disk locations for data).
• pg_transform: Stores type transformation functions for procedural languages.
• pg_trigger: Stores triggers on tables.
• pg_ts_config: Stores full-text search configurations.
• pg_ts_config_map: Maps text search config tokens to dictionaries.
• pg_ts_dict: Stores text search dictionaries.
• pg_ts_parser: Defines tokenizers for full-text search.
• pg_ts_template: Defines templates for building text search dictionaries.
• pg_type: Stores all data types (built-in, custom, enum, composite). (IMPORTANT)
• pg_user_mapping: Maps users to foreign servers.

pg_catalog.pg_database details

this docs can be found in https://www.postgresql.org/docs/16/catalog-pg-database.html

management script collection

- show all databases (in current user)

SELECT * FROM pg_catalog.pg_database;`:

- show pg_catalog.pg_tables definition

go back on top in order to see what actually view is

SELECT pg_get_viewdef('pg_catalog.pg_tables', true);`: 

- show all available pgsql datatype

SELECT 
	*
FROM
	pg_catalog.pg_type;

- lists all schema

SELECT
    *
FROM
    pg_catalog.pg_namespace;

- show all dbs with owner

SELECT 
        x.oid as object_id,
        x.datname as db_name,
        CASE 
                WHEN pg_catalog.pg_get_userbyid(x.datdba) LIKE 'unknown (OID=%)' THEN 'UNKNOWN'
                ELSE pg_catalog.pg_get_userbyid(x.datdba)
        END as owner
FROM pg_catalog.pg_database as x;

cond: https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-CASE

- RENAME db (as postgres user)

ALTER DATABASE xyz RENAME TO abc;