PostgreSQL – List Databases

Background

Let us list databases.

Code

Outline

  1. 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


select 

          tblD.datname as database

        , ltrim
            (
                tblD.oid::text
                , ','
            ) as databaseID

        -- , tblD.dattablespace as tablespaceIDDefault

        , tblTSD.spcname as tableSpaceDefault

        -- , tblD.datdba as ownerID

        , tblAuth.rolname as "owner"

        --, tblD.encoding
        --  as encodingID

        , pg_catalog.pg_encoding_to_char
            (
                tblD.encoding
            )
            as "encoding"

        , tblD.datcollate

        , pg_size_pretty
            (
                pg_database_size
                (
                    -- datname::text
                    tblD.oid
                )
            ) as "databaseSize"

        , (pg_stat_file
            (
                'base'
                ||'/'
                ||tblD.oid
                ||'/'
                ||'PG_VERSION'
            )
          )
          .modification
          --.creation
          AS datecreated

        , tblD.datistemplate

        , tblD.datallowconn

from   pg_catalog.pg_database tblD

inner join pg_catalog.pg_tablespace tblTSD

        on tblD.dattablespace = tblTSD.oid

left outer join pg_catalog.pg_authid tblAuth

        on tblD.datdba = tblAuth.oid

order by
    tblD.datname asc 

Output

pg_catalog.pg_database.01.20190812.1255AM

References

  1. 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


select
          current_database()
            as "database"

        , tblN.nspname
            as "schema"

        , tblC.relname
            as "name"

        , tblAuth.rolname
            as "owner"

        , tblC.reltablespace
            as "tableSpace"

        , tblC.reltuples
            as "numberofRecords"

        , tblC.relpages
            as "numberofPages"

        -- use pg_relation_size::oid
        , pg_relation_size
            (
                tblC.oid
            )
            AS totalBytes

        -- use pg_relation_size::namespace||.||relname
        , pg_relation_size
            (
                cast
                (
                    quote_ident
                    (
                        tblN.nspname
                    )
                    || '.'
                    || quote_ident
                    (
                        tblC.relname
                    )
                    as text
                )
            ) AS totalBytesAlt

        -- use relationPages * current_setting('block_size')
        , tblC.relpages
            * (
                current_setting('block_size') ::bigint
              )
            AS totalBytesCalc

        , pg_size_pretty
            (
                pg_relation_size
                (
                    tblC.oid
                ) :: bigint
            )   as totalBytesPretty 

        , tblC.relkind
                as "relationKind"

        , case tblC.relkind

                when 'r' then 'table'
                when 'i' then 'index'
                when 'S' then 'Sequence'
                when 'v' then 'View'
                when 'm' then 'materialized view'
                when 't' then 'toast'
                when 'f' then 'Foreign Table'

          end
                as "objectType"

        , tblC.relpersistence
                as "relationPersistence"

        , case tblC.relpersistence

                when 'p' then 'Permanent Table'
                when 'u' then 'Unlogged Table'
                when 't' then 'Temporary Table'

          end as "persistency"

from pg_catalog.pg_class tblC

join pg_catalog.pg_namespace tblN

    on tblC.relnamespace = tblN.oid

join pg_catalog.pg_authid tblAuth

    on tblC.relowner = tblAuth.oid

where tblC.relkind in
        (
            -- r = ordinary table
            -- , i = index, S = sequence, v = view, m = materialized view, c = composite type, t = TOAST table, f = foreign table
              'r'
            , 'v' -- view
            , 'm' -- Materialized View
            , 't' -- TOAST Table
            , 'p'
        )

and   tblN.nspname not in
        (
              'information_schema'
            , 'pg_catalog'
            , 'pg_toast'
        )

order by     

          tblN.nspname 

        , tblC.relname 

    ; 

Output

Output – Northwind

pg_catalog.pg_class.northwind.01.20190730.0116AM

Output – Sample Database

pg_catalog.pg_class.lab.dataRange.01.20190730.0115AM

Summary

Here is a quick rundown of the functions we used :-

  1. pg_relation_size
    • Returns table size
    • Accepts
      • pg_catalog.pg_class.oid ( object id )
      • or pg_catalog.pg_class.relname ( relation name )
      • or pg_catalog.pg_namespace || ‘.’ || pg_catalog.pg_class.relname ( schema name + relation name )
  2. current_setting(‘block_size’)
    • Current Block Size
    • Multiply by Number of Pages
  3. pg_size_pretty
    • Converts raw bytes to human readable format ( kb, mb, gb )

PostgreSQL – List Users

Background

Let us list principals and roles for our PostgreSQL Instance.

Code

Schema :- pg_catalog

Table :- pg_authid

SQL


select 

		  tblPCA.rolname as "role"

		, tblPCA.rolsuper as "superUser"  

		, tblPCA.rolcreaterole as "createRole"

		, tblPCA.rolcreatedb as "createDB"

		, tblPCA.rolcanlogin as "canLogin"		

		, tblPCA.rolconnlimit as "connectionLimit"	

		, tblPCA.rolpassword  as "passwordHash"

		, tblPCA.rolvaliduntil as "validUntil"

from  pg_catalog.pg_authid tblPCA

Output

pg_catalog.pg_authid.20190729.0202PM

 

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.

  1. information_schema
    • information_schema.tables
  2. pg_catalog
    • pg_catalog.pg_tables
    • pg_catalog.pg_class

Query

information_schema

information_schema.tables

SQL


select 

	      current_database() as "database"

	   , t.table_catalog

   	   , t.table_schema

       , t.table_name

       , t.table_type

from    information_schema.tables t

where   t.table_schema not in
 		(
 			  'pg_catalog'
 	        , 'information_schema'
 	    )

order by
	 t.table_catalog
       , t.table_schema
       , t.table_name

; 

Output

information_schema.tables.01.20190727.0329PM

pg_catalog

pg_catalog.pg_tables

SQL


select
	      t.schemaname

     	, t.tablename

        , t.tableowner

        , t.tablespace

        , t.hasindexes

        , t.hasrules

        , t.hastriggers

        , t.rowsecurity

from pg_catalog.pg_tables t 

where t.schemaname not in
 		(
 		    'pg_catalog'
 	           , 'information_schema'
 	        )

order by     

          t.schemaname

     	, t.tablename

;

Output

information_schema.tables.01.20190727.0329PM

pg_catalog.pg_class

SQL

select
	      current_database() as "database"

	    , tblN.nspname as "schema"

	    , tblC.relname as "name"

	    , tblAuth.rolname as "owner"

    	, tblC.reltablespace as "tableSpace"

    	, tblC.relpages as "numberofPages"

    	, tblC.relkind  as "relationKind"

    	/*
    	 	  r = ordinary table
    	 	, i = index
    	 	, S = sequence
    	 	, v = view
    	 	, m = materialized view
    	 	, c = composite type
    	 	, t = TOAST table
    	 	, f = foreign table
       */
    	, case tblC.relkind

    			when 'r' then 'table'
    			when 'i' then 'index'
    			when 'S' then 'Sequence'
    			when 'v' then 'View'
    			when 'm' then 'materialized view'
    			when 't' then 'toast'
    			when 'f' then 'Foreign Table'

          end
          		as "objectType"

    	, tblC.relpersistence
    			as "relationPersistence"

    	/*
    	 	  p = permanent table
    	 	, u = unlogged table
    	 	, t = temporary table
    	*/
    	, case tblC.relpersistence

    			when 'p' then 'Permanent Table'
    			when 'u' then 'Unlogged Table'
    			when 't' then 'Temporary Table'

          end as "persistency"

from pg_catalog.pg_class tblC

join pg_catalog.pg_namespace tblN

	on tblC.relnamespace = tblN.oid

join pg_catalog.pg_authid tblAuth

	on tblC.relowner = tblAuth.oid

where tblC.relkind in
		(
			  'r' -- table
			, 'v' -- view
			, 'm' -- materialized view
	        )

and   tblN.nspname not in
 		(
 	            'pg_catalog'
 	          , 'information_schema'
 	        )

;

Output

pg_catalog.pg_class.01.20190727.0345PM

References

  1. PostgreSQL.org
    • Documentation
      • System Catalog
      • Information Schema
        • The Information Schema
          Link