Regex and PostgreSQL practice

You know FILTER, GENERATE_SERIES, and STRING_AGG. Now let us add PostgreSQL regex to your toolkit and practice combining everything.

PostgreSQL supports full regular expressions, which are more powerful than LIKE for complex pattern matching.

pg_regex.sql

sql

-- PostgreSQL regex matching
SELECT DISTINCT customer_city
FROM customers
WHERE customer_city ~ '^sao'
LIMIT 10;

-- ~  = case-sensitive regex match
-- ~* = case-insensitive regex match
-- !~ = does NOT match regex

PostgreSQL supports full regex with ~ and ~* operators. More powerful than LIKE for complex patterns.

Performance is identical since PostgreSQL optimizes both the same way. FILTER is purely a readability improvement. COUNT(*) FILTER (WHERE status = 'delivered') is much clearer than SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END).

Fill in the blanks: PostgreSQL functions

Loading practice…

Matching exercise: Match the pg function

Loading practice…

Quiz: Quiz

Loading practice…

Code playground: Monthly revenue with Zero-fill

Loading practice…