Read System Tables

PostgreSQL contains a series of system tables with information about the database objects, system configuration, and other information about database systems. Read access to the schema is required to read system tables.

Some common PostgreSQL system tables:

pg_namespace: Stores namespaces.
information_schema.tables: Used for maintaining information on tables in a database.
information_schema.columns: Used for maintaining information on columns in a database.

MicroStrategy accesses the system tables that hold information about tables, column, or data types in the catalog. See the SQL example below.

Copy

SELECT DISTINCT TABLE_SCHEMA as NAME_SPACE, TABLE_NAME as TAB_NAME from INFORMATION_SCHEMA.TABLES; 
 

SELECT DISTINCT nspname as NAME_SPACE, relname as TAB_NAME, attname as COL_NAME, typname as DATA_TYPE, COALESCE(B.CHARACTER_MAXIMUM_LENGTH, attlen) as DATA_LEN, NUMERIC_PRECISION as DATA_PREC, NUMERIC_SCALE as DATA_SCALE FROM pg_attribute A JOIN pg_class C ON A.attrelid = C.oid JOIN pg_namespace N on C.relnamespace = N.oid JOIN pg_type T on A.atttypid = T.oid JOIN INFORMATION_SCHEMA.COLUMNS B on A.attname = B.COLUMN_NAME AND C.relname = B.TABLE_NAME AND N.nspname = B.TABLE_

You can also define a custom SQL query to read from system tables to satisfy their unique requirements.