PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 97 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 relatively small amount of data, which is in the system catalog of a database.
  • Statistics about the catalog content and project home in GitHub that has additional information.

# Name ▲ Goal Type Data source Last update License
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 2025-11-07 10:11 MIT License View
2 All event triggers Find event triggers, which are not associated to a specific schema object. General system catalog base tables only 2025-11-07 10:11 MIT License 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 2025-11-07 10:11 MIT License 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 2025-11-07 10:11 MIT License 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 2025-11-07 10:11 MIT License 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 2025-11-13 12:53 MIT License View
7 Database objects of the same type and case insensitive name in the same container Find database objects with the same type and case insensitive name in the same container. It can only happen if some of the names are case insensitive and others are case sensitive. For instance, the same schema can contain the table "Client" and client Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
8 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 2025-11-07 10:11 MIT License View
9 Different tasks of triggers 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. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
10 Disabled rules Identify disabled rules. These should be enabled or dropped, otherwise these are dead code. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
11 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 2025-11-07 10:11 MIT License View
12 Disabled user triggers Identify disabled triggers. These should be enabled or dropped, otherwise these are dead code. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
13 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 2025-11-07 10:11 MIT License View
14 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 2025-11-07 10:11 MIT License View
15 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 2025-11-07 10:11 MIT License View
16 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 2025-11-07 10:11 MIT License View
17 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 2025-11-07 10:11 MIT License View
18 Find mixed usage of \w and [[:alnum:]] regex syntax 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. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-15 11:11 MIT License View
19 Inconsistent digit character class syntax in regular expressions 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. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-25 17:13 MIT License View
20 Inconsistent referencing to character classes Find as to whether regular expressions use inconsistently references to character classes: [^\s], [^\d], [^\w], [^[:space:]], [^[:digit:]], [^[:word:]] vs [^\S], [^\D], [^\W]. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View