PostgreSQL – List Databases

Background Let us list databases. Code Outline Access pg_catalog.pg_database pg_catalog.pg_tablespace Default Table space Name pg_database.dattablespace = pg_tablespace.oid pg_catalog.pg_authid Authorizer pg_database.datdba = pg_authid.oid pg_catalog.pg_encoding_to_char Encoding pg_catalog.pg_encoding_to_char(pg_database.encoding ) pg_catalog.pg_stat_file Database Creation Date pg_catalog.pg_stat_file ( 'base' ||'/' ||pg_database.oid ||'/' ||'PG_VERSION' ) SQL Output References PostgreSQL Functions and Operators System Administrative Functions Link

PostgreSQL :- Get Table Size

Background Getting database table sizes is a good way to familiarize oneself with a couple of PostgreSQL functions. Code SQL Output Output - Northwind Output - Sample Database Summary Here is a quick rundown of the functions we used :- pg_relation_size Returns table size Accepts pg_catalog.pg_class.oid ( object id ) or pg_catalog.pg_class.relname ( relation name … Continue reading PostgreSQL :- Get Table Size

PostgreSQL :- List Tables

Background Let us quickly list tables. Outline The Information Schema is an ANSI compliant Schema and works well across all database vendors. pg_catalog is PostgreSQL native. Here are the tables and views that we will use in this exercise. information_schema information_schema.tables pg_catalog pg_catalog.pg_tables pg_catalog.pg_class Query information_schema information_schema.tables SQL Output pg_catalog pg_catalog.pg_tables SQL Output pg_catalog.pg_class SQL … Continue reading PostgreSQL :- List Tables