PostgreSQL Query Catalog

Show user-defined triggers if there are more than 9 different trigger routine bodies, i.e., different triggers on different tables that do the same thing count as one trigger.

Find all CHECK constraints (except NOT NULL) of domains that are not associated with any column.

Find all tables that rows have been physically sorted based on an index.

Find all the default values of base table, view, and foreign table columns.

Find all columns that have a dynamic default value, i.e., the value is returned by a function but the function is not for invoking a sequence.

Find all columns that have a static default value, i.e., the value is not returned by a function.

Find all covering indexes, which include data from additional columns in leaf blocks.

Find the privileges to use the database. c - CONNECT; C - CREATE; T - TEMPORARY.

Find partitioned tables that have been implemented by using the declarative approach. Declarative partitioning is implemented in PostgreSQL starting from PostgreSQL 10.

This query identifies complex derived tables (views and materialized views) that perform data integration operations. Specifically, it filters for views whose definition involves joining two or more distinct tables. This distinguishes non-trivial views—which encapsulate relationship logic and data aggregation—from simple projection views that merely mirror a single base table. The result highlights the core reporting and data integration layer of the schema.

Find domains that specify a default values and columns that are defined based on the domain. Make sure that there are no unsuitable default values.

Find all domains.

Find all enumerated types.

Find event triggers, which are not associated to a specific schema object.

Find all exclude constraints.

Enforce referential integrity in database. Find all referential integrity (foreign key) constraints.

Find all foreign tables.

Find indexes with less common access methods. Gin indexes are, for instance, used to speed up PostgreSQL's built in full text search.

Find all non-identifying relationships where the foreign key (a set of columns) is a subset of a uniqueness constraint (primary key, unique, or exclude constraint).

This query retrieves a comprehensive list of all PRIMARY KEY and UNIQUE constraints defined on base tables within the database. These constraints are the fundamental mechanisms for enforcing entity integrity and uniqueness. The output provides essential information for schema auditing, documentation generation, and analyzing the data model's key structures. It allows administrators and developers to quickly verify how uniqueness is enforced for each table.