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 event triggers, which are not associated to a specific schema object.

Find user-defined rules for rewriting data manipulation language statements. Rules should be used only for the tasks that cannot be achieved in a declarative manner, i.e., for example, by declaring a constraint.

Find user-defined triggers that react to data modifications in tables. Triggers should be used only for the tasks that cannot be achieved in a declarative manner, i.e., by declaring a constraint. Triggers of the same table with the same event_manipulation, action_timing, and action_orientation are sorted based on the trigger name. This is the order of execution of triggers.

Make sure that Boolean expressions take into account precedence rules of Boolean operators. AND operator has precedence over OR operator.

This query identifies all expressions that use the non-standard length() function. Although length() is a functional synonym for char_length() in PostgreSQL, its use is discouraged for two primary reasons: char_length() is the SQL-standard function, and length() has different semantics in other database systems (e.g., returning byte length in MySQL). To enhance code portability and prevent semantic ambiguity for developers, this query flags all instances of length() to encourage standardization on the char_length() function.

This query identifies database objects of the same type within the same schema (or container) that have identical names (and parameters in case of routines) when ignoring case. In SQL databases, this confusing scenario typically occurs when mixing quoted (case-sensitive) and unquoted (case-insensitive) identifiers. For example, a single schema could contain both a table named "Client" and another table named client.

Find different tasks that are solved by using rules, i.e., different rules on the same table or different tables that do the same thing are considered to solve one task.

Find different tasks that are solved by using triggers, i.e., different triggers on the same table or different tables that do the same thing are considered to solve one task.

Identify disabled rules. These should be enabled or dropped, otherwise these are dead code.

These triggers should be enabled because otherwise some important functionality regarding constraints like enforcing referential integrity does not work.

Identify disabled triggers. These should be enabled or dropped, otherwise these are dead code.

Write code that is simple to understand and not confusing. A double negative is a grammatical construction occurring when two forms of negation are used in the same expression (https://en.wikipedia.org/wiki/Double_negative). Double negatives in Boolean expressions make it more difficult to understand and maintain the code.

Fing regular expression patterns that use [^\S] instead of \s or [^\D] instead of \d or [^\W] instead of \w.

Find multiple rules with the same definition (event, condition, action) on the same table. Do remember that the same task can be solved in SQL usually in multiple different ways. Thus, the exact copies are not the only possible duplication.

Find regular expressions where within the same specification of a character class the character class alnum as well as 0-9, \d, A-Z, or a-z has been defined.

Find cases where the same table has multiple triggers with the same type (row-level, statement-level) that react to the same event with the same WHEN condition and with the same way (by invoking the same function).

This query audits regular expressions within the database to detect inconsistent syntax for matching alphanumeric characters. It checks for the concurrent use of both the Perl-style shorthand \w and the POSIX character class [[:alnum:]]. While these are often functionally similar, their exact behavior can differ based on locale settings (e.g., \w may include underscores while [[:alnum:]] does not). Using both styles within the same codebase indicates a lack of a clear standard, which can lead to maintainability issues and subtle, locale-dependent bugs. Standardizing on a single, well-understood syntax is recommended for clarity and predictability.

This query audits regular expressions within the database to detect syntactical inconsistencies in identifying numeric digits. It checks for the concurrent use of disparate character class notations: range-based ([0-9]), Perl-style shorthand (\d), and POSIX character classes ([[:digit:]]). While these are often functionally equivalent for standard ASCII digits, mixing multiple syntaxes within a single codebase indicates a lack of standardization, which reduces code readability and increases cognitive load during maintenance.

Find as to whether regular expressions use inconsistently references to character classes: [^\s], [^\d], [^\w], [^[:space:]], [^[:digit:]], [^[:word:]] vs [^\S], [^\D], [^\W].