PostgreSQL :- Listing Column Names

Background Let us expose the columns in each of our tables. Code Outline Column Length The Column length is one of the more perplexing attribute The reason being the existence of a couple of columns that intertwine to properly decode it Attributes attlen ( Attribute Length ) atttypid ( Attribute Type ID ) atttypmod ( … Continue reading PostgreSQL :- Listing Column Names

PostgreSQL :- Average Row Length for a table

Background Wanted to put together a short SQL for calculating Average Row Length for each table. Code Outline To get the Average Row Length, we do the following :- Get Table Size pg_relation_size against table object pg_class.oid or pg_class.relpages * pg_catalog.pg_settings.value ( name = 'page_size') Get Number of records pg_class.rel_tuples   SQL Output Output #1 … Continue reading PostgreSQL :- Average Row Length for a table

PostgreSQL – Metadata – Index Columns

Background Admitting nothing real, nor original. I am just trying to close out this Chrome Tab that showcases the best of Stack Overflow. Stack Overflow Luke Francl List columns with indexes in PostgreSQL Link SQL Outline Tables pg_catalog.pg_class pg_catalog.pg_namespace pg_class.relnamespace = pg_namespace.oid pg_catalog.pg_index pg_class.relnamespace = pg_index.indrelid pg_catalog.pg_attribute pg_attribute.attrelid = pg_index.indrelid and pg_attribute.attnum = any(pg_index.indkey) Columns … Continue reading PostgreSQL – Metadata – Index Columns

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