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:

  • 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.

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:

  • (?=[^'']*(?<!\\)[[:upper:]]) – verifies that the pattern contains at least one uppercase letter in any position.

  • (?=[^'']*(?<!\\)[[:lower:]]) – verifies that the pattern contains at least one lowercase letter in any position.



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
WITH expressions AS (select 
n.nspname as schema,
c.relname || '.' || o.conname as name, 
substring(pg_get_constraintdef(o.oid),7) as expression,
'TABLE CHECK' AS type
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_namespace n on n.oid=c.relnamespace
where o.contype ='c'  
and n.nspname not in (select schema_name
from information_schema.schemata
where schema_name<>'public' and
schema_owner='postgres' and schema_name is not null)
union select n.nspname as schema,
t.typname || '.' || o.conname as name, 
substring(pg_get_constraintdef(o.oid),7) as expression,
'DOMAIN CHECK' AS type
from pg_constraint o inner join pg_type t on t.oid = o.contypid
inner join pg_namespace n on n.oid=t.typnamespace
where o.contype ='c' and n.nspname not in (select schema_name
from information_schema.schemata
where schema_name<>'public' and
schema_owner='postgres' and schema_name is not null)
UNION SELECT trigger_schema, event_object_table || '.' || trigger_name, action_condition, 'TRIGGER WHEN' AS type
FROM INFORMATION_SCHEMA.triggers
WHERE action_condition IS NOT NULL
UNION SELECT schemaname, tablename || '.' || rulename, definition, 'RULE WHERE' AS type
FROM pg_catalog.pg_rules
WHERE schemaname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
UNION SELECT table_schema, table_name, view_definition, type
FROM (SELECT  table_schema, table_name, regexp_replace(view_definition,'[\r\n]','<br>','g') AS view_definition, 'VIEW SUBQUERY' AS type
FROM information_schema.views
WHERE table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
UNION SELECT schemaname, matviewname, regexp_replace(definition,'[\r\n]','<br>','g') AS definition, 'MATERIALIZED VIEW SUBQUERY' AS type
FROM pg_catalog.pg_matviews) AS foo
WHERE view_definition ~*'^.+([[:space:]]+where[[:space:]]+|[[:space:]]+having[[:space:]]+).+$'
UNION SELECT pg_namespace.nspname AS routine_schema,  pg_proc.proname || '(' || pg_get_function_identity_arguments(pg_proc.oid)  || ')' AS routine_name,  regexp_replace(pg_get_functiondef(pg_proc.oid),'[\r\n]','<br>','g')  AS routine_src, 'ROUTINE' AS type
FROM pg_catalog.pg_proc, pg_catalog.pg_namespace
WHERE pg_proc.pronamespace = pg_namespace.oid 
AND pg_proc.prokind<>'a'
AND pg_proc.proname NOT IN ('f_assume_you_must_use_files', 'f_check_format_comma_separated_list', 'f_check_password', 'f_default_value_with_no_match') 
AND pg_namespace.nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL) 
AND NOT EXISTS (SELECT 1
FROM pg_catalog.pg_depend d 
WHERE EXISTS (SELECT 1 FROM pg_catalog.pg_extension e WHERE d.refobjid=e.oid) AND
d.objid=pg_proc.oid))
SELECT schema, name AS object_identifier, type AS object_type, expression AS suspected_expression
FROM expressions
WHERE expression ~ '!?~\*[[:space:]]*\(?[[:space:]]*E?''(?=[^'']*(?<!\\)[[:upper:]])(?=[^'']*(?<!\\)[[:lower:]])[^'']*'''
ORDER BY schema, type, name;

Fixing Suggestion

Determine whether the regular expression is meant to be case-sensitive or case-insensitive:

  • 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 (replace a-zA-Z with a-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 .

NameDescription
Find problems automaticallyQueries, 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

NameDescription
CHECK constraintsQueries of this category provide information about CHECK constraints.
Derived tablesQueries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer.
DomainsQueries of this category provide information about reusable specifications of column properties.
Regular expressionsQueries of this catergory provide information about the use of regular expressions.
Triggers and rulesQueries of this category provide information about triggers and rules in a database.
User-defined routinesQueries of this category provide information about the user-defined routines