Filter Queries
- All the queries about database objects contain a subcondition to exclude from the result information about the system catalog.
- Although the statements use SQL constructs (common table expressions; NOT in subqueries) that could cause performance problems in case of large datasets, it shouldn't be a problem in case of the relatively small amount of data in the system catalog of a database.
#1. A large number of triggers
INFORMATION_SCHEMA+system catalog base tablesShow 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.
#2. All event triggers
system catalog base tables onlyFind event triggers, which are not associated to a specific schema object.
#3. All rules
system catalog base tables onlyFind 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.
#4. All user triggers that are associated with tables
INFORMATION_SCHEMA+system catalog base tablesFind 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.
#5. AND takes precedence over OR
INFORMATION_SCHEMA+system catalog base tablesMake sure that Boolean expressions take into account precedence rules of Boolean operators. AND operator has precedence over OR operator.
#6. Avoid using length function
INFORMATION_SCHEMA+system catalog base tablesThis 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.
#7. BEFORE validation triggers inappropriately depending on pg_trigger_depth
INFORMATION_SCHEMA+system catalog base tablesThis query identifies BEFORE triggers whose primary purpose is to enforce data integrity and business rules (validating data and raising exceptions without performing any modifying DML operations), but which conditionally bypass execution based on the trigger nesting level (pg_trigger_depth()). Database invariants and business rules must be enforced unconditionally, regardless of whether a data modification was initiated directly by an external client query or indirectly via another trigger, stored procedure, or cascading operation. Because pure validation triggers perform only read operations and do not execute INSERT, UPDATE, DELETE, or MERGE statements, it is mathematically impossible for them to cause infinite recursion. Therefore, incorporating pg_trigger_depth() into validation logic is a severe architectural flaw that creates a critical data integrity loophole: whenever secondary triggers modify the table, all essential validity checks are silently skipped.
#8. Case-insensitive regular expressions (~* or !~*) containing both uppercase and lowercase letters
INFORMATION_SCHEMA+system catalog base tablesThis query identifies database objects (table and domain CHECK constraints, trigger WHEN conditions, rules, views, materialized views, and user-defined routines) that use a case-insensitive regular expression operator (~* or !~*), yet contain both uppercase and lowercase letters inside the regular expression pattern (for example, col ~* '^[A-Z][a-z]+$', col ~* '^[ab].*[C-D]', or col ~* '^[a-zA-Z0-9]+$').
Combining a case-insensitive operator with a mixed-case regular expression pattern indicates one of two issues:
- Incorrect operator (broken case validation): The developer intended to enforce distinct case rules for different parts of the string (such as requiring an uppercase letter in one position and a lowercase letter in another, e.g.,
'^[C].*[a-z]'or'^\$2[abxy]\$[0-9]{2}\$[./A-Za-z0-9]{53}$'), but mistakenly used~*(or!~*) instead of the case-sensitive operator~(or!~). Because~*disables case sensitivity across the entire pattern, the intended case restrictions are silently bypassed. - Redundant or inconsistent pattern style: The developer genuinely intended case-insensitive matching, but redundantly specified both letter cases (such as
a-zA-Z) or mixed uppercase and lowercase characters inconsistently within the pattern.
#9. Column references in DELETE trigger exceptions
INFORMATION_SCHEMA+system catalog base tablesThis query identifies DELETE trigger routines that raise an exception and explicitly specify a column in the error context (using the COLUMN clause). This is generally a logical mismatch, as a DELETE operation affects an entire row rather than individual columns. Providing a column-specific error context in this scenario often indicates a copy-paste error from an INSERT or UPDATE trigger.
#10. Database objects of the same type and case insensitive name in the same container
INFORMATION_SCHEMA+system catalog base tablesThis 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.
#11. Different tasks of rules
system catalog base tables onlyFind 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.
#12. Disabled rules
system catalog base tables onlyIdentify disabled rules. These should be enabled or dropped, otherwise these are dead code.
#13. Disabled system triggers (i.e., disabled enforcement of constraints)
system catalog base tables onlyThese triggers should be enabled because otherwise some important functionality regarding constraints like enforcing referential integrity does not work.
#14. Disabled user triggers
system catalog base tables onlyIdentify disabled triggers. These should be enabled or dropped, otherwise these are dead code.
#15. Distinct functional tasks implemented by triggers
INFORMATION_SCHEMA+system catalog base tablesThis query identifies and categorizes the distinct functional tasks implemented via triggers across the database schema. Rather than simply listing individual trigger instances, it aggregates triggers on the same or multiple tables that execute identical logic or share the same underlying routine (for example, maintaining audit timestamps or logging change history) into a single functional responsibility. This high-level inventory provides developers and database architects with a clear overview of the responsibilities delegated to the database tier, helping identify opportunities for code consolidation and standardization.
#16. Double negatives in Boolean expressions
INFORMATION_SCHEMA+system catalog base tablesWrite 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.
#17. Double negatives in regular expressions
INFORMATION_SCHEMA+system catalog base tablesFing regular expression patterns that use [^\S] instead of \s or [^\D] instead of \d or [^\W] instead of \w.
#18. Duplicate rules
system catalog base tables onlyFind 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.
#19. Duplicate specification of character classes
INFORMATION_SCHEMA+system catalog base tablesFind 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.
#20. Duplicate triggers
INFORMATION_SCHEMA+system catalog base tablesFind 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).
| # | Name(sorted ascending, activate to sort descending) | Goal(activate to sort ascending) | Type(activate to sort ascending) | Data source(activate to sort ascending) | Last update(activate to sort ascending) | License | Actions |
|---|---|---|---|---|---|---|---|
| 1 | A large number of triggers | 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. | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 2 | All event triggers | Find event triggers, which are not associated to a specific schema object. | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 3 | All rules | 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. | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 4 | All user triggers that are associated with tables | 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. | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 5 | AND takes precedence over OR | Make sure that Boolean expressions take into account precedence rules of Boolean operators. AND operator has precedence over OR operator. | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 6 | Avoid using length function | 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. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 7 | BEFORE validation triggers inappropriately depending on pg_trigger_depth | This query identifies BEFORE triggers whose primary purpose is to enforce data integrity and business rules (validating data and raising exceptions without performing any modifying DML operations), but which conditionally bypass execution based on the trigger nesting level (pg_trigger_depth()). Database invariants and business rules must be enforced unconditionally, regardless of whether a data modification was initiated directly by an external client query or indirectly via another trigger, stored procedure, or cascading operation. Because pure validation triggers perform only read operations and do not execute INSERT, UPDATE, DELETE, or MERGE statements, it is mathematically impossible for them to cause infinite recursion. Therefore, incorporating pg_trigger_depth() into validation logic is a severe architectural flaw that creates a critical data integrity loophole: whenever secondary triggers modify the table, all essential validity checks are silently skipped. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 8 | Case-insensitive regular expressions (~* or !~*) containing both uppercase and lowercase letters | This query identifies database objects (table and domain CHECK constraints, trigger WHEN conditions, rules, views, materialized views, and user-defined routines) that use a case-insensitive regular expression operator (~* or !~*), yet contain both uppercase and lowercase letters inside the regular expression pattern (for example, col ~* '^[A-Z][a-z]+$', col ~* '^[ab].*[C-D]', or col ~* '^[a-zA-Z0-9]+$').Combining a case-insensitive operator with a mixed-case regular expression pattern indicates one of two issues:
|
Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 9 | Column references in DELETE trigger exceptions | This query identifies DELETE trigger routines that raise an exception and explicitly specify a column in the error context (using the COLUMN clause). This is generally a logical mismatch, as a DELETE operation affects an entire row rather than individual columns. Providing a column-specific error context in this scenario often indicates a copy-paste error from an INSERT or UPDATE trigger. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 10 | Database objects of the same type and case insensitive name in the same container | 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. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 11 | Different tasks of rules | 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. | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 12 | Disabled rules | Identify disabled rules. These should be enabled or dropped, otherwise these are dead code. | Problem detection | system catalog base tables only | MIT (opens in new tab) | View | |
| 13 | Disabled system triggers (i.e., disabled enforcement of constraints) | These triggers should be enabled because otherwise some important functionality regarding constraints like enforcing referential integrity does not work. | Problem detection | system catalog base tables only | MIT (opens in new tab) | View | |
| 14 | Disabled user triggers | Identify disabled triggers. These should be enabled or dropped, otherwise these are dead code. | Problem detection | system catalog base tables only | MIT (opens in new tab) | View | |
| 15 | Distinct functional tasks implemented by triggers | This query identifies and categorizes the distinct functional tasks implemented via triggers across the database schema. Rather than simply listing individual trigger instances, it aggregates triggers on the same or multiple tables that execute identical logic or share the same underlying routine (for example, maintaining audit timestamps or logging change history) into a single functional responsibility. This high-level inventory provides developers and database architects with a clear overview of the responsibilities delegated to the database tier, helping identify opportunities for code consolidation and standardization. | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 16 | Double negatives in Boolean expressions | 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. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 17 | Double negatives in regular expressions | Fing regular expression patterns that use [^\S] instead of \s or [^\D] instead of \d or [^\W] instead of \w. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 18 | Duplicate rules | 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. | Problem detection | system catalog base tables only | MIT (opens in new tab) | View | |
| 19 | Duplicate specification of character classes | 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. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 20 | Duplicate triggers | 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). | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View |