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

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s