| Goal | This query identifies database objects (table and domain CHECK constraints, trigger WHEN conditions, rules, views, materialized views, and user-defined routines) that contain regular expressions specifying both lowercase and uppercase ASCII letter ranges (a-zA-Z or A-Za-z) where the case-insensitive regular expression operator (~* or !~*) with a single range (a-z) can be used instead.Writing a-zA-Z or A-Za-z is unnecessary and verbose in two situations:
Regular expressions that use a case-sensitive operator ( ~ or !~) alongside other case-specific characters or character classes (for example, '^\$2[abxy]\$[0-9]{2}\$[./A-Za-z0-9]{53}$', where [abxy] must remain strictly lowercase) are excluded, because replacing ~ with ~* would alter the validation semantics of the rest of the pattern. |
|---|---|
| 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 flags an expression if it matches either of two regular expressions:
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 | Low Many false-positive results |
| License | MIT (opens in new tab) |
| Data Source | INFORMATION_SCHEMA+system catalog |
| SQL Query |
|
Fixing Suggestion
a-zA-Z (or A-Za-z) with a-z in the character class and use the case-insensitive regular expression operator ~* (or !~*) instead of ~ (or !~). For example, rewrite col ~ '^[a-zA-Z0-9]+$' as col ~* '^[a-z0-9]+$'. 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 |