PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 32 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 predefine character class has been incorrectly specified Find regular expressions where a predefined character class is incorrectly specified, e.g. [digit] instead of [:digit:]. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
2 CHECK constraint with pattern matching on non-textual columns This query identifies a semantic mismatch between data types and constraint logic. It targets base and foreign table columns that are defined with non-textual data types (e.g., INTEGER, DATE, BOOLEAN) but are subject to single-column CHECK constraints utilizing string pattern matching operators (LIKE, SIMILAR TO, or regular expressions). This practice forces implicit casting to text, which is computationally inefficient and indicates a design flaw. It suggests that either the column should utilize a textual data type, or the constraint should be rewritten using operators appropriate for the actual data type (e.g., numeric ranges instead of regex). Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-05 19:13 MIT License View
3 Double checking of the maximum character length This query identifies superfluous CHECK constraints where a programmatic length check duplicates a declarative, data type-based length limit. For instance, a CHECK constraint like char_length(column) <= 100 on a column already defined as VARCHAR(100) is redundant. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-13 13:14 MIT License View
4 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
5 Duplication of case insensitivity specification in a regular expression Find regular expressions that use both case insensitive search operator ~* and case insensitivity modifier (?i). Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
6 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
7 Find pointless regular expressions that match any value This query identifies regular expressions that are tautological, specifically those that effectively match any non-NULL string, such as ^.*$. It is superfluous because it evaluates to TRUE for any non-NULL value, including an empty string. It provides no actual data validation and acts as a no-operation (no-op) check. Such patterns are often artifacts of placeholder code, incomplete logic, or a fundamental misunderstanding of regular expression syntax. Removing them improves clarity and eliminates a useless computational step. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-15 11:16 MIT License View
8 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
9 Inconsistent regex character class syntax usage This query audits regular expressions across the database to detect inconsistent syntax when defining character classes. It specifically checks for the concurrent usage of Perl-style shorthand notation (e.g., \s, \d) and POSIX character classes (e.g., [[:space:]], [[:digit:]]). While often functionally overlapping, these syntaxes may have subtle behavioral differences depending on locale and engine versions. The presence of both styles within a single database indicates a lack of coding standards, reducing readability and increasing maintenance complexity. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-20 13:12 MIT License View
10 Incorrect specification of logical or in regular expressions Find the use of regular expressions where logical or is incorrectly specified, i.e., (| or |). Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
11 Incorrect suffix of a constraint name or an index name If the name of an object has the suffix that refers to the type of the object (for instance, primary key constraint or foreign key constraint), then you should use references to the correct object type. Find suffixes of constraint names and index names that incorrectly refer to the type of the object. For instance, incorrect would be to use _chk as the suffix of an index name or _pk as the suffix of a check constraint name. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
12 Invalid character class PostgreSQL regular expressions do not have character classes word and letter. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
13 Invalid use of the case insensitive search modifier in regular expressions Find regular expression patterns that use (?i) modifier in any other place than at the beginning of the pattern or (?-i) in any place of the pattern. Such use of the modifiers is not supported by PostgreSQL. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
14 LIKE instead of = Find expressions that use LIKE predicate for precise comparison. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
15 LIKE with a regular expression pattern Find expressions that use LIKE (including ILIKE) predicate with a regular expression patterns. In a LIKE pattern one can use only _ and % metasymbols to construct a pattern. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
16 Names of character classes are not in the lowercase Find regular expressions where the names of character classes are not completely in lowercase. For instance, incorrect is to write [[:UPPER:]] or [[:Upper:]] and correct is [[:upper:]]. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
17 Non-predefined character classes must not be between double square brackets Write correct regular expressions. For instance, if there is a rule that code must consist of one or more digits, then correct expression is code~'^[0-9]+$', not code~'^[[0-9]]+$'. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
18 Perhaps a regular expression could be simplified Find regular expressions that name character classes a-zA-Z. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
19 Perhaps brackets are missing in a regular expression that uses OR logical operation Find regular expressions where choice between alternatives has no brackets. Thus, instead of '(a|b|c)' there is 'a|b|c'. An example: '^a|b|c$' -permits in the string symbol "|" but '^(a|b|c)$' does not permit in the string symbol "|". Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
20 Perhaps checking of file extension is incorrect Find check constraints of tables that use a regular expression to check as to whether a registered string ends with an appropriate file extension. However, the expression does not put the dot sign into the square brackets nor does have the escape character \before it, i.e., it is interpreted as a single character not as the dot sign in the expression. In regular expressions the dot (.) matches any single character except the newline character. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View