Greenplum System Catalog Views and Information Schema

In Greenplum, system catalog views and the information schema provide valuable metadata and information about the database, tables, indexes, and other objects. These views are essential for understanding the structure and properties of the database, as well as for querying information about the database objects. Here are some important Greenplum system catalog views and information schema tables:

 1. System Catalog Views:

 1.1 pg_tables:

   - Displays information about tables in the database.

   

   SELECT * FROM pg_tables;

   

 1.2 pg_indexes:

   - Provides information about indexes on tables.

   

   SELECT * FROM pg_indexes;

   

 1.3 pg_views:

   - Lists information about views in the database.

   

   SELECT * FROM pg_views;

   

 1.4 pg_namespace:

   - Contains information about database schemas.

   

   SELECT * FROM pg_namespace;

   

 1.5 pg_roles:

   - Shows information about database roles (users and groups).

   

   SELECT * FROM pg_roles;

   

 1.6 pg_database:

   - Provides information about databases.

   

   SELECT * FROM pg_database;

   

 1.7 pg_attribute:

   - Contains information about table columns.

   

   SELECT * FROM pg_attribute WHERE attrelid = 'your_table'::regclass;

   

 1.8 pg_stats:

   - Displays statistics about tables and their columns.

   

   SELECT * FROM pg_stats WHERE tablename = 'your_table';

   

 1.9 pg_constraint:

   - Shows information about table constraints.

   

   SELECT * FROM pg_constraint WHERE conrelid = 'your_table'::regclass;

   

 1.10 pg_triggers:

   - Provides information about triggers on tables.

   

   SELECT * FROM pg_triggers WHERE tgrelid = 'your_table'::regclass;

   

 2. Information Schema:

 2.1 information_schema.tables:

   - Contains information about tables, views, and their properties.

   

   SELECT * FROM information_schema.tables;

   

 2.2 information_schema.columns:

   - Provides information about table columns.

   

   SELECT * FROM information_schema.columns WHERE table_name = 'your_table';

   

 2.3 information_schema.views:

   - Lists information about views.

   

   SELECT * FROM information_schema.views;

   

 2.4 information_schema.schemata:

   - Contains information about database schemas.

   

   SELECT * FROM information_schema.schemata;

   

 2.5 information_schema.key_column_usage:

   - Shows information about columns used in key constraints.

   

   SELECT * FROM information_schema.key_column_usage WHERE table_name = 'your_table';

   

 2.6 information_schema.table_constraints:

   - Lists information about table constraints.

   

   SELECT * FROM information_schema.table_constraints WHERE table_name = 'your_table';

   

 2.7 information_schema.columns:

   - Provides information about table columns.

   

   SELECT * FROM information_schema.columns WHERE table_name = 'your_table';

   

 2.8 information_schema.triggers:

   - Displays information about triggers on tables.

   

   SELECT * FROM information_schema.triggers WHERE event_object_table = 'your_table';

   

 2.9 information_schema.statistics:

   - Provides statistics about table columns.

   

   SELECT * FROM information_schema.statistics WHERE table_name = 'your_table';

   

 3. Other Useful Queries:

 3.1 Show Databases:

   - Displays a list of available databases.

   

   \l

   

 3.2 Show Tables:

   - Lists tables in the current database.

   

   \dt

   

 3.3 Describe Table:

   - Describes the structure of a table.

   

   \d your_table

   

These system catalog views and information schema tables provide insights into the database structure, objects, and metadata. They are valuable tools for administrators, developers, and analysts working with Greenplum to understand, query, and optimize the database schema and objects.