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_catalog.pg_database details#

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

Column Name	Type	Description
`datname`	`name`	The name of the database.
`datdba`	`oid`	The OID of the role (user) that owns the database. Use `pg_get_userbyid(datdba)` to resolve it.
`encoding`	`int`	The character encoding of the database (e.g., UTF8 = 6). Use `pg_encoding_to_char(encoding)` to get the name.
`datlocprovider`	`char`	Locale provider used (`c` = libc, `i` = ICU).
`datistemplate`	`bool`	If `true`, the database can be used as a template for `CREATE DATABASE ... TEMPLATE`.
`datallowconn`	`bool`	If `false`, connections to this database are not allowed (except by superusers).
`datconnlimit`	`int`	The maximum number of concurrent connections allowed (-1 = no limit).
`datlastsysoid`	`oid`	The last system OID used in this database at creation (mainly historical).
`datfrozenxid`	`xid`	The transaction ID at which all tuples are known to be frozen (related to VACUUM).
`datminmxid`	`xid`	The minimum multixact ID that is still considered potentially unfrozen.
`dattablespace`	`oid`	OID of the default tablespace for the database. Use `pg_tablespace` to resolve.
`datcollate`	`name`	LC_COLLATE setting (how strings are sorted).
`datctype`	`name`	LC_CTYPE setting (how character classification works).
`daticulocale`	`text`	ICU locale (used if `datlocprovider = 'i'`).
`datcollversion`	`text`	Version of the collation used (important for collation versioning with ICU).
`datacl`	`aclitem[]`	Access privileges (GRANTs), stored as an array of ACL items.

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;