PostgreSQL Query Catalog

Find foreign servers that do not have any associated foreign tables.

Find indexes that are not used by the DBMS. Remember that indexes are not a "free lunch" and they slow down the processes of updating data.

Find indexes that are not used by the DBMS. Remember that indexes are not a "free lunch" and they slow down the processes of updating data.

Find named input parameters that are not referenced in the routine body. All the parameters that are presented in the routine signature declaration must be used in its body. Otherwise these are dead code elements.

Do not keep in your database elements that are not needed by anybody. These should be put in use or dropped, otherwise these are dead code.

Do not keep in your database elements that are not needed by anybody. These should be put in use or dropped, otherwise these are dead code.

This query identifies automatically updatable views that lack the WITH CHECK OPTION clause. Without this constraint, it is possible to perform INSERT or UPDATE operations through the view that create rows which do not satisfy the view's defining predicate (the WHERE clause). This results in "phantom" modifications where the new or updated data is successfully committed to the base table but is immediately excluded from the view's result set. Enforcing WITH CHECK OPTION ensures that all data modifications performed through the view remain visible within the view.

This query identifies views that are automatically updatable by the database engine but lack explicit safeguards to prevent data modification. Specifically, it targets views that meet the criteria for auto-updatability (typically simple projections of a single base table) yet are missing an INSTEAD OF trigger or a DO INSTEAD NOTHING rule. Without these mechanisms, any INSERT, UPDATE, or DELETE operation performed against the view will seamlessly propagate to the underlying base table, which may violate the intended read-only design contract.

This query identifies automatically updatable views that define a row restriction (via a WHERE clause) but lack the WITH CHECK OPTION constraint. In the absence of this constraint, it is possible to perform INSERT or UPDATE operations through the view that result in rows satisfying the base table constraints but failing the view's inclusion criteria. This leads to "phantom updates," where the modified data is committed to the database but immediately disappears from the view's scope. Enforcing WITH CHECK OPTION ensures that all modifications performed through the view respect its defining predicate.

Find triggers that try prevent updating data in a certain column but prevent also certain legal updates - updates that write to a field a value that was in the field before the update.

Find UPDATE triggers where updated columns are not specified. These triggers could be executed too often because unneeded executions are not prevented.

Find UPDATE triggers where WHEN clause is not specified. These triggers could be executed too often because unneeded executions are not prevented.

This query identifies user-defined routines that contain unbounded Data Modification Language (DML) statements. Specifically, it flags routines containing UPDATE or DELETE operations that lack a qualifying WHERE clause. Such statements result in full-table modifications, affecting every row in the target relation. While valid in specific maintenance contexts, this pattern typically represents a critical logic error in transactional code, posing a severe risk of unintended massive data loss or corruption.

This query identifies all expressions that use the non-standard now() function. In PostgreSQL, now() is a historical, non-standard alias for the SQL-standard current_timestamp. While they are functionally identical within PostgreSQL (both returning the transaction start timestamp as a TIMESTAMPTZ), the use of current_timestamp is strongly preferred for reasons of code portability and adherence to standards. Standardizing on current_timestamp ensures the code is universally understood and easier to maintain or migrate to other database systems.

Be precise and write as little code as possible. Prefer expressions with simple invocations of functions like localtimestamp, current_timestamp, and current_date over expressions like (now())::date. Find table columns that have a default value that casts the type of the returned value of a non-deterministic function (now, localtimestamp, current_timestamp, and current_date).

This query identifies user-defined routines (excluding triggers) that are functionally trivial. It flags routines whose body consists solely of returning a static value: either an input argument (identity function), a constant literal, or NULL. Such routines typically perform no computation, data manipulation, or side effects. They are likely placeholders, deprecated logic, or artifacts of incomplete refactoring, and should be reviewed for removal or implementation.

This query identifies trigger functions that are functionally trivial, specifically those whose sole action is to execute RETURN NEW. In a BEFORE trigger context, this operation simply allows the data modification to proceed unchanged. If the function contains no other logic (e.g., validation, modification of NEW, or side effects), it performs no useful work and incurs unnecessary execution overhead. Such triggers are likely incomplete placeholders or obsolete code that should be removed.

Find columns and parameters where the type of the identifier is perhaps explicitly indicated in the name.

Find columns and parameters where the type of the identifier is perhaps explicitly indicated in the name.

You should follow the principle of least privilege and thus not have in your database user-defined routines that execution privilege is granted to PUBLIC, i.e., to all the database users now and in the future. By default, PostgreSQL gives routine execution privileges to PUBLIC.