Pattern matching, ranges and IN

The last of the filtering tools: matching text by shape, and asking for ranges. Both are easy, and both have a performance consequence that module 7 will explain and that is worth knowing about now.

LIKE

Two wildcards, and only two:

%    any sequence of characters, including none
_    exactly one character

SELECT title FROM books WHERE title LIKE 'The %' ORDER BY title;

              title
----------------------------------
 The God of Small Things
 The Guide
 The Home and the World
 The Hungry Tide
 The Inheritance of Loss
 The Ministry of Utmost Happiness
 The Namesake
 The Room on the Roof
 The Shadow Lines
(9 rows)

The underscore is the one people forget exists:

SELECT name FROM members WHERE name LIKE '_____ %' ORDER BY name LIMIT 5;

     name
--------------
 Arjun Pawar
 Deepa Apte
 Kunal Kelkar
 Meera Naik
 Pooja Jadhav
(5 rows)

Five characters, then a space, then anything — members whose first name is exactly five letters.

LIKE is case-sensitive. PostgreSQL adds ILIKE, which is not:

SELECT count(*) AS like_lower FROM books WHERE title LIKE  '%the%';   -- 4
SELECT count(*) AS ilike_any  FROM books WHERE title ILIKE '%the%';   -- 11

Four against eleven. LIKE '%the%' missed every title starting with "The", because that T is a capital. That gap is the bug, and it is invisible unless you count.

ILIKE is a PostgreSQL extension. Standard SQL uses LOWER(title) LIKE '%the%', which works everywhere and has the same index problem described below.

Matching a literal % or _

WHERE note LIKE '100\%%'        -- starts with "100%"
WHERE note LIKE '100!%%' ESCAPE '!'

The default escape is a backslash; ESCAPE chooses another when backslashes are awkward.

The performance thing

WHERE title LIKE 'The %'     -- can use a normal index
WHERE title LIKE '%Small%'   -- cannot

An index on text is sorted, so the database can jump to everything starting with The . A pattern starting with % has no known beginning, so there is nothing to jump to — it must check every row.

On 40 books, irrelevant. On five million, it is the difference between milliseconds and seconds. ILIKE and LOWER(col) LIKE have the same problem for the same reason.

The fixes exist and are module 7's: a trigram index (pg_trgm) makes %middle% searches indexable, and PostgreSQL's full-text search is the right answer when a human is typing words. Mentioned now so that LIKE '%…%' in a hot query rings a bell.

SIMILAR TO and regular expressions

WHERE title SIMILAR TO 'The (Guide|Namesake)'
WHERE title ~  '^The .*s$'      -- regex, case-sensitive
WHERE title ~* '^the'           -- regex, case-insensitive
WHERE title !~ 'Days'           -- regex, not matching

~ is POSIX regular expressions and is what you will actually use if LIKE is not enough. SIMILAR TO is a standard-SQL halfway house that almost nobody uses.

Do not reach for a regex when LIKE will do. It is slower, harder to read, and has the same index problem.

BETWEEN, again, and the timestamp trap

From the filtering lesson, repeated because it is the mistake:

SELECT title, price_paise FROM books
WHERE price_paise BETWEEN 40000 AND 50000
ORDER BY price_paise;

          title          | price_paise
-------------------------+-------------
 The Hungry Tide         |       40500
 An Equal Music          |       41000
 Unaccustomed Earth      |       42500
 The Inheritance of Loss |       44000
 The God of Small Things |       45000
 Sea of Poppies          |       48500
(6 rows)

Inclusive at both ends. Fine for integers and for date.

Wrong for timestamp:

-- misses everything after midnight on the 30th
WHERE borrowed_at BETWEEN DATE '2026-09-01' AND DATE '2026-09-30'

-- correct
WHERE borrowed_at >= DATE '2026-09-01' AND borrowed_at < DATE '2026-10-01'

A date used as a timestamp means midnight. 2026-09-30 14:22 is after 2026-09-30 00:00:00, so BETWEEN excludes almost the entire last day — silently, and it looks right until somebody checks a month-end total.

Half-open ranges — >= the start and < the day after — for anything with a time. It is the same rule as slicing in most programming languages, and it composes: consecutive months cover every instant exactly once with no gaps or overlaps.

Dates and intervals

SELECT count(*) AS overdue FROM loans
WHERE returned_on IS NULL AND due_on < DATE '2026-09-27';

 overdue
---------
      40

Forty loans are out and past their due date.

Date arithmetic uses interval:

WHERE borrowed_on > current_date - interval '30 days'
WHERE due_on <= current_date + interval '1 week'
SELECT due_on - borrowed_on AS loan_days FROM loans LIMIT 1;    -- an integer, for dates
SELECT age(current_date, date_of_birth) FROM members LIMIT 1;   -- an interval

Subtracting two dates gives an integer number of days. Subtracting two timestamps gives an interval. That asymmetry surprises people once.

Use current_date rather than typing today's date, so the query keeps meaning what you meant. And note current_date is evaluated in the server's timezone, which the Full-Stack course has a whole lesson about — a query run at 00:15 IST can disagree with one run at 23:45 UTC about which day it is.

IN with a list, and its limits

SELECT name, membership FROM members
WHERE membership IN ('student', 'senior')
ORDER BY name LIMIT 5;

     name       | membership
----------------+------------
 Aditya Kale    | senior
 Amit Ranade    | student
 Anjali Gokhale | student
 Arjun Pawar    | senior
 Deepa Apte     | student
(5 rows)

Fine for a handful of values. For thousands — generated from an application — it is slow to parse and can exceed limits. Join against a table or use = ANY(array) instead, which module 9 covers.

And the NOT IN warning from the previous lesson stands: NOT EXISTS, always.

Choosing

An exact value                      =
A handful of values                 IN
A range of numbers or dates         >= and <
A range, inclusive both ends        BETWEEN — never for timestamps
Text starting with something        LIKE 'x%'       — indexable
Text containing something           ILIKE '%x%'     — not indexable without pg_trgm
Text matching a shape               ~ (regex)       — last resort
Words a human typed                 full-text search — module 7

Check your work

The two LIKE wildcards. % for any sequence, _ for exactly one character.

Why LIKE '%the%' found 4 and ILIKE found 11. LIKE is case-sensitive, so it missed every title starting with "The".

Which LIKE patterns can use an index. Those with a known beginning — 'The %' yes, '%Small%' no.

What fixes %middle% searching. A trigram index, or full-text search when a human is typing.

When to use a regex. When LIKE genuinely cannot express it.

Whether BETWEEN includes its endpoints. Both.

Why BETWEEN is wrong for timestamps. It excludes almost all of the final day.

The pattern to use instead. Half-open: >= the start, < the day after.

What subtracting two dates gives. An integer of days — two timestamps give an interval.

Why current_date rather than a literal. The query keeps meaning what you meant — and it depends on the server's timezone.

When IN with a list stops being appropriate. At thousands of values.

Practice

1. Find every title starting with "The". Then every title containing "the" in any case. Explain the difference in count.
2. Find members whose first name is exactly four letters.
3. Find books whose title ends in "s".
4. Write a LIKE that matches a literal %.
5. Write the same search as LIKE, as ILIKE, and as LOWER(...) LIKE. Note which is portable.
6. Use a regex to find titles that are exactly two words.
7. Find books priced between ₹300 and ₹400 inclusive. Confirm the endpoints are included.
8. Write a one-month date range with BETWEEN, then as a half-open range. Construct a row that the first misses.
9. Count loans overdue today using current_date.
10. Find loans borrowed in the last 90 days.
11. Subtract borrowed_on from due_on and note the type. Do the same with two timestamps.
12. Compute each member's age with age().
13. Take the choosing table above and write one query for each row against this data.

Official documentation

• PostgreSQL — Pattern matching — LIKE, SIMILAR TO and POSIX regular expressions, with the escape rules.
• PostgreSQL — pg_trgm — Making %middle% searches indexable.
• PostgreSQL — Full text search — The right answer when a human is typing words.
• PostgreSQL — Date/time functions and operators — interval, age(), current_date and the subtraction rules.
• PostgreSQL — Row and array comparisons — IN, ANY and their array forms.

---

You can now get rows out of one table: choose columns, filter them, order them, page through them, and handle the fact that some values are unknown.

Next module: turning many rows into one number.