PostgreSQL :- Reading Files Through Large Objects

Background Discovered someone has a Sacred Text available on the web. It is an NIV version of the bible. It is availed as an XML file.   Load Command Table Definition Sample Import into Text Column SQL Output Explanation The XML file is read in Broken into separate records Import into XML Column SQL Output … Continue reading PostgreSQL :- Reading Files Through Large Objects

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 :- Datatypes

Background Datatypes is one of key building blocks of a database system. Common Ones Here are the some of the common ones in PostgreSQL. Bool Values true false Character pg_catalog::name Internal Type for Object Names 64 bytes Character information_schema::sql_identifier ANSI Compatible Char Fixed Length Varchar Variable Length text Variable Length unlimited in size Datetime Date … Continue reading PostgreSQL :- Datatypes

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 :- Function/Metadata

Background Now that we have a couple of functions written, let us see how to review metadata on functions. Code SQL Output GUI DBeaver Outline To review function properties, please do the following :- Launch DBeaver Connect to PostgreSQL Instance Navigate to Schema \ {Schema} \ Functions \ {Function} Function Parameters Navigate on Selection From … Continue reading PostgreSQL :- Function/Metadata

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 – 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