Basics of selection I · Topic 8 Free
Regular expressions
Data model — the tables you will query
How the tables join
| Relationship | Cardinality | Join condition |
|---|---|---|
| students.cohort_id → cohorts.id | many students → one cohort | JOIN cohorts ON students.cohort_id = cohorts.id |
| enrollments.student_id → students.id | many enrollments → one student | JOIN students ON enrollments.student_id = students.id |
| enrollments.course_id → courses.id | many enrollments → one course | JOIN courses ON enrollments.course_id = courses.id |
| job_offers.student_id → students.id | many job offers → one student | JOIN students ON job_offers.student_id = students.id |
In short: each student belongs to one cohort. Students enroll in courses through enrollments, and their job offers land in job_offers.
Regular expressions (regex) describe text patterns far more precisely than LIKE. Postgres supports them natively.
The operators
| Operator | Meaning |
|---|---|
~ | matches regex (case-sensitive) |
~* | matches regex (case-insensitive) |
!~ | does not match |
!~* | does not match (case-insensitive) |
SELECT name FROM students WHERE name ~ '^[AM]';Core regex syntax
| Pattern | Matches |
|---|---|
. | any single character |
^ / $ | start / end of the string |
[abc] | one of a, b, c |
[a-z], [0-9] | character ranges |
+ | one or more of the previous |
* | zero or more |
? | zero or one |
(ab|cd) | ab or cd |
Examples:
WHERE title ~ '[0-9]' -- contains a digit
WHERE name ~ '^A.*a$' -- starts with A, ends with a
WHERE role ~* '(analyst|engineer)' -- contains either word, any caseUnlike LIKE, a regex matches anywhere in the string unless you anchor it with ^ and $.
When to use what
- Exact value →
= - Starts with / contains / ends with →
LIKE/ILIKE(simpler and faster) - Anything more complex → regex
Regex also powers extraction and cleanup functions — REGEXP_REPLACE(), SUBSTRING(text FROM pattern) — which you will meet when cleaning messy real-world data. Data engineers use these constantly.
Practice
A real Postgres database runs in your browser. Solve the tasks, or just experiment.