How to Compare URLs with Mixed Case-Sensitivity in PostgreSQL

Understanding URL Case Sensitivity Rules

According to RFC 3986 guidelines, URLs are split into distinct components with varying rules for case-sensitivity:

• Scheme (e.g., https://) and Hostname (e.g., EXAMPLE.COM): Case-insensitive. HTTP://EXAMPLE.COM is identical to http://example.com.
• Userinfo, Path, Query Parameters, and Fragments: Case-sensitive. /Article/ is different from /article/.

Because standard PostgreSQL functions like LOWER() alter the entire string, applying them directly destroys case sensitivity in paths or user info. To compare and index URLs correctly according to RFC specifications, you need to canonicalize URLs by lowercasing only the scheme and host components.

Solution: Custom IMMUTABLE Function for Canonicalization

The cleanest approach in PostgreSQL is to create a custom IMMUTABLE function that uses regular expressions to parse, lowercase the scheme and host, and reconstruct the URL. Since the function is IMMUTABLE, PostgreSQL allows you to index its result for ultra-fast lookups.

Step 1: Create the URL Canonicalization Function