PostgreSQL Regular Expression Operators (~ and ~*)

The ~ and ~* operators in PostgreSQL provide POSIX regular expression pattern matching. ~ performs case-sensitive matching, while ~* is case-insensitive. These operators offer the most powerful and flexible pattern matching capabilities in PostgreSQL.

Syntax

-- Case-sensitive regex match
column ~ 'pattern'
column !~ 'pattern'    -- Does NOT match

-- Case-insensitive regex match
column ~* 'pattern'
column !~* 'pattern'   -- Does NOT match (case-insensitive)

Official Documentation

Example usage

-- Basic pattern matching
SELECT * FROM users
WHERE email ~ '@gmail\.com$';
-- Matches emails ending with @gmail.com

-- Case-insensitive match
SELECT * FROM products
WHERE name ~* 'iphone|android';
-- Matches 'iPhone', 'ANDROID', 'android phone', etc.

-- Digit matching
SELECT * FROM orders
WHERE order_id ~ '^[0-9]+$';
-- Matches strings containing only digits

-- Character classes
SELECT * FROM users
WHERE username ~ '^[a-z][a-z0-9_]{2,15}$';
-- Username: starts with letter, 3-16 chars, letters/digits/underscore

-- Email validation
SELECT * FROM contacts
WHERE email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$';

-- Phone number patterns
SELECT * FROM customers
WHERE phone ~ '^\(?[0-9]{3}\)?[-.\s]?[0-9]{3}[-.\s]?[0-9]{4}$';
-- Matches: (555)123-4567, 555-123-4567, 555.123.4567, etc.

-- NOT match operator
SELECT * FROM products
WHERE description !~ '[0-9]';
-- Products without numbers in description

-- URL validation
SELECT * FROM links
WHERE url ~ '^https?://[a-zA-Z0-9]';

-- IP address matching
SELECT * FROM servers
WHERE ip ~ '^([0-9]{1,3}\.){3}[0-9]{1,3}$';

-- Word boundaries
SELECT * FROM articles
WHERE content ~ '\mPostgreSQL\M';
-- Matches whole word "PostgreSQL", not "PostgreSQL's" or "MyPostgreSQL"

-- Quantifiers
SELECT * FROM codes
WHERE code ~ '^[A-Z]{3}-[0-9]{4,6}$';
-- 3 uppercase letters, dash, 4-6 digits

-- Alternation
SELECT * FROM files
WHERE filename ~ '\.(jpg|jpeg|png|gif)$';
-- Image files only

-- Grouping and capturing (just for matching, not extraction)
SELECT * FROM dates
WHERE date_string ~ '^(19|20)[0-9]{2}-(0[1-9]|1[0-2])-(0[1-9]|[12][0-9]|3[01])$';
-- YYYY-MM-DD format

-- Start and end anchors
SELECT * FROM usernames
WHERE username ~ '^admin';      -- Starts with "admin"
SELECT * FROM usernames
WHERE username ~ 'admin$';      -- Ends with "admin"

-- Case-insensitive with ~*
SELECT * FROM articles
WHERE title ~* '\m(postgres|postgresql)\M';
-- Case-insensitive match for "postgres" or "postgresql"

-- Character class negation
SELECT * FROM passwords
WHERE password ~ '[^a-zA-Z0-9]';
-- Contains special characters

-- Repetition
SELECT * FROM data
WHERE value ~ '^[0-9]+\.[0-9]{2}$';
-- Decimal number with exactly 2 decimal places

-- Multiple conditions
SELECT * FROM users
WHERE email ~ '@[a-z]+\.(com|org|net)$'
  AND username ~* '^[a-z]';

-- Extract with regexp_match (returns array)
SELECT regexp_match(email, '@([a-z]+\.[a-z]+)$') AS domain
FROM users;

-- Replace with regexp_replace
SELECT regexp_replace(phone, '[^0-9]', '', 'g') AS cleaned_phone
FROM customers;

Common issues

1. Escaping: Special regex chars (. * + ? [ ] ( ) { } ^ $ | \) need escaping with \ to match literally.
2. Performance: Complex regex on large tables without indexes can be very slow.
3. Dot matches: . matches any character except newline. Use [.\n] or flags for newline matching.
4. Greedy vs lazy: * and + are greedy. Use *? and +? for lazy matching.

Best practices

1. Use ~* for case-insensitive searches instead of converting with LOWER().
2. Anchor patterns with ^ (start) and $ (end) when matching full strings.
3. For simple wildcards, use LIKE (faster). Use regex for complex patterns.
4. Test regex patterns thoroughly; they can be tricky and may match unexpected values.

Frequently Asked Questions

Q: What's the difference between ~ and ~*?
A: ~ is case-sensitive: 'Hello' ~ 'hello' is false. ~* is case-insensitive: 'Hello' ~* 'hello' is true.

Q: Can I extract matched groups from regex?
A: Use regexp_match() function: SELECT regexp_match(email, '@(.+)$') returns an array with captured groups. For multiple matches, use regexp_matches().

Q: How do I match a literal dot or other special character?
A: Escape with backslash: ~ '\.pdf$' matches strings ending with ".pdf". The dot without backslash matches any character.

Q: Are PostgreSQL regex patterns Perl-compatible (PCRE)?
A: No, PostgreSQL uses POSIX Extended Regular Expressions (ERE). They're similar but not identical to PCRE. No lookaheads, lookbehinds, or some other PCRE features.

Q: Can ~ use indexes?
A: Not directly with standard B-tree indexes. Create a trigram index (pg_trgm extension) for better performance: CREATE INDEX idx_name ON table USING GIN (column gin_trgm_ops).