PostgreSQL Query Catalog

This query identifies row-level BEFORE and INSTEAD OF triggers that explicitly RETURN NULL. In PostgreSQL's trigger execution model, this return value acts as a cancellation signal. For BEFORE triggers on tables, it aborts the operation for the current row, preventing the INSERT, UPDATE, or DELETE and suppressing subsequent triggers. For INSTEAD OF triggers on views, it signals that no modification was performed. While this behavior can be used for conditional logic (e.g., silently ignoring invalid rows), it presents a risk of unintended data loss or logic errors if used incorrectly. These triggers should be audited to ensure the cancellation behavior is intentional and correctly implemented.

Find rule names that are used in a database in more than one schema. Different things should have different names. But here different rules have the same name. Also make sure that this is not a duplication.

Find derived tables (views and materialized views) that use a set theoretic operation (union, except or intersect) in a manner that does not remove duplicate rows and thus can produce a multiset not a set. Make sure that it is what is needed.

This query identifies primary keys that consist of a single column and are not system-generated (i.e., they are not associated with a sequence or defined as IDENTITY columns). This pattern is characteristic of a natural primary key, where the key's value is derived from a real-world, user-defined attribute rather than an arbitrary surrogate value. Identifying these keys is crucial for auditing a data model's key strategy and understanding its reliance on meaningful, potentially mutable, business data for entity identification.

Find tables that have one column or zero columns.

Find columns that probably store content of files in the database.

Find surrogate keys. Surrogate key is a key that consist of one column, which has an integer type. The key has been declared by using PRIMARY KEY or UNIQUE constraint. The column is associated with a sequence generator (either external or internal, i.e., created by the system automatically because the column has been declared as an identity column). The column does not participate in any foreign key.

Find all CHECK constraints (except NOT NULL) that are associated with a base table or a foreign table column and use a regular expression. It is useful to enforce as many constraints at database level as possible. In this way one improves data quality as well as gives extra information to the database users (including the DBMS engines, development environments, and applications).

Surrogate key values must be generated by using the system (the sequence generator mechanism in case of PostgreSQL). If there is no usage of sequence generators, then there is a question as to whether there are no surrogate keys in the database at all (could be possible and OK) or (more probable) developers have forgotten to implement the generation of surrogate keys.

Find constraints that involve more than one columns. Check as to whether the names follow a common style or not.

Find tables that contain both a state and a status column.

Find tables that contain multiple columns for free-form textual descriptions. Make sure that the names of columns are understandable and sufficiently different. Make sure that there are no duplicate columns.

Find inheritance between base tables. Use table inheritance carefully because, for instance, certain constraints are not inherited and must be redefined on child tables.

Find in case of each base table that participates in a table inheritance hierarchy the path to the table from the top-level table. Use table inheritance carefully because, for instance, certain constraints are not inherited and must be redefined on child tables. Also make sure that the identifier of each child table in an inheritance hierarchy is a hyponym of the identifier of its parent table.

Check as to whether there are no unnecessary privileges.

Find information about tables that are associated with triggers.

Find tables with the same name in different schemas. Make sure that this is not a duplication.

Find the TOP 3 longest (identifiers) names of user-defined objects.

Find what database object names are used more than once in case the objects of the same type. If the names differ from each other only by digits or underscores, then consider these the same name. For instance, if there are base tables Person and Person2 (in the same schema or different schemas), then the query returns the name Person. Make sure that there is no duplication of implementation elements in the database.

Find trigger functions that are used in case of more than one table. Although it is legal, one must be careful when changing the functions in order to avoid unwanted consequences.