Postgres System Queries for Retrieving Postgres Database Object Information
The following examples contain information on how to retrieve database information for Postgres objects such as tables, views, indexes, schemas, databases, functions, and triggers. PostgreSQL provides an information_schema schema that contains views that return information about Postgre objects. If the user has the appropriate access, the user can also query tables or views in the pg_catalog schema to get information about Postgres objects. See the examples below for more information.
Tables
Postgres table information can be retrieved either from the information_schema.tables view, or from the pg_catalog.pg_tables view. Below are example queries:
select * from information_schema.tables;
select * from pg_catalog.pg_tables;
Schemas
This query will get the user's currently selected schema:
select current_schema();
These queries will return all schemas in the database:
select * from information_schema.schemata;
select * from pg_catalog.pg_namespace
Databases
This query will get the user's currently selected database:
select current_database();
This query will return all databases for the server:
select * from pg_catalog.pg_database
Views
These queries will return all views across all schemas in the database:
select * from information_schema.views
select * from pg_catalog.pg_views;
Columns for Tables
This query will return column information for a table named employee:
SELECT
*
FROM
information_schema.columns
WHERE
table_name = 'employee'
ORDER BY
ordinal_position;
Indexes
This query will return all index information in the database:
select * from pg_catalog.pg_indexes;
Functions
This query will return all functions in the database. For user-defined functions, the routine_definition column will have the function body:
select * from information_schema.routines where routine_type = 'FUNCTION';
Triggers
This query will return all triggers in the database. The action_statement column contains the trigger body:
select * from information_schema.triggers;
Listed below are links to articles containing system queries for other databases:
Cassandra: Cassandra System Queries
Firebird: Firebird System Queries
Microsoft SQL Server: SQL Server System Queries
MySQL: MySQL System Queries
Oracle: Oracle System Queries
SQLite: SQLite System Queries
Sybase: Sybase System Queries
