| Goal | 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:
|
|---|---|
| Notes | Refers to the column pg_proc.prokind and thus works starting from PostgreSQL 11. The query collects expressions across table CHECK constraints, domain CHECK constraints, trigger WHEN clauses, rules, views, materialized views, and user-defined routines (excluding aggregate functions, system schemas, postgres-owned non-public schemas, and extension objects).The WHERE clause uses a case-sensitive regular expression (~) to find any occurrence of the ~* or !~* operator followed by a single-quoted pattern string that satisfies two independent positive lookaheads:
A negative lookbehind ( (?<!\\)) ensures that PostgreSQL regular expression escape sequences preceded by a backslash (such as \M, \S, \D, \W, \A, and \Z) are not mistaken for literal uppercase or lowercase letters. In the returned definitions of views, materialized views, and routines, newline characters are replaced with HTML line break (<br>) tags for better readability in a web browser. |
| Type | Problem detection Each row in the result could represent a flaw in the design |
| Reliability | Medium Medium number of false-positive results |
| License | MIT (opens in new tab) |
| Data Source | INFORMATION_SCHEMA+system catalog |
| SQL Query |
|
Fixing Suggestion
- If the pattern must enforce specific uppercase and lowercase characters in different positions (e.g.,
'^[ab].*[C-D]'or'^[A-Z][a-z]+$'), replace the case-insensitive operator~*(or!~*) with the case-sensitive operator~(or!~). - If the matching is genuinely intended to be case-insensitive, keep
~*(or!~*), remove redundant ranges (replacea-zA-Zwitha-z), and write all literal letters and character ranges in the pattern using a uniform case (e.g., lowercase).
Collections
This query belongs to the following collections:
Find problems automatically
Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .
| Name | Description |
|---|---|
| Find problems automatically | Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not . |
Categories
This query is classified under the following categories:
CHECK constraints
Queries of this category provide information about CHECK constraints.
Derived tables
Queries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer.
Domains
Queries of this category provide information about reusable specifications of column properties.
Regular expressions
Queries of this catergory provide information about the use of regular expressions.
Triggers and rules
Queries of this category provide information about triggers and rules in a database.
User-defined routines
Queries of this category provide information about the user-defined routines
| Name | Description |
|---|---|
| CHECK constraints | Queries of this category provide information about CHECK constraints. |
| Derived tables | Queries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer. |
| Domains | Queries of this category provide information about reusable specifications of column properties. |
| Regular expressions | Queries of this catergory provide information about the use of regular expressions. |
| Triggers and rules | Queries of this category provide information about triggers and rules in a database. |
| User-defined routines | Queries of this category provide information about the user-defined routines |