Window functions, full-text search and regex

Window frames, ROW_NUMBER and LAG, running totals, tsvector search with ranking, pg_trgm fuzzy matching and pattern matching options.

Window functions

select
  customer_id,
  issued_at,
  total_cents,
  row_number() over w                    as seq,
  sum(total_cents) over w                as running_total,
  avg(total_cents) over (partition by customer_id) as customer_avg,
  lag(total_cents) over w                as previous_total,
  total_cents - lag(total_cents) over w  as delta,
  rank() over (order by total_cents desc) as size_rank
from invoice
where issued_at >= now() - interval '1 year'
window w as (partition by customer_id order by issued_at
             rows between unbounded preceding and current row)
order by customer_id, issued_at;

Function	Answers	Notes
`row_number()`	Position in the partition	Unique even with ties
`rank()` / `dense_rank()`	Competition ranking	Rank leaves gaps, dense_rank does not
`lag()` / `lead()`	Previous or next row	Accepts an offset and a default
`first_value()` / `last_value()`	Boundary values	`last_value` needs an explicit frame or it returns the current row
`ntile(n)`	Even buckets	Useful for quartiles and sampling

💡

Without a frame clause, the default is range between unbounded preceding and current row, which includes all rows tied on the ordering value. For a running total that is usually wrong: use rows between ... to count rows rather than values.

Full-text search

alter table book add column search tsvector
  generated always as (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(description, '')), 'B')
  ) stored;

create index book_search_idx on book using gin (search);

select id, title,
       ts_rank(search, q)          as rank,
       ts_headline('english', description, q,
         'StartSel=<mark>, StopSel=</mark>, MaxWords=25') as snippet
from book, websearch_to_tsquery('english', 'distributed systems -slavery') q
where search @@ q
order by rank desc
limit 20;

to_tsquery requires explicit operators; plainto_tsquery ANDs the words; websearch_to_tsquery accepts what a user types, including quoted phrases and -exclusion.
A generated tsvector column keeps the index in sync automatically - far safer than a trigger you can forget.
Use setweight so a hit in the title outranks one in the body, and a GIN index so @@ stays fast.
Stemming is language-specific: index with the same configuration you query with, or matching silently degrades.
For fuzzy or typo-tolerant matching add pg_trgm and a trigram index; for semantic search, pgvector.

create extension if not exists pg_trgm;
create index book_title_trgm on book using gin (title gin_trgm_ops);

select title, similarity(title, 'dispossesed') as sim
from book
where title % 'dispossesed'          -- similarity above the threshold
order by sim desc
limit 10;

Pattern matching

select '2026-09-18' ~ '^\d{4}-\d{2}-\d{2}$';                 -- boolean
select regexp_replace('a  b   c', '\s+', ' ', 'g');           -- normalise whitespace
select regexp_matches('order-42-item-7', 'order-(\d+)-item-(\d+)');  -- array of groups
select regexp_split_to_table('a,b,,c', ',');
select substring('ISBN 978-0-441' from '([0-9-]+)');

select id from invoice where number like 'INV-2026-%';         -- anchored prefix, index friendly
select id from invoice where number ilike '%2026%';            -- case-insensitive, no index use

Operator	Meaning	Index friendly
`like 'abc%'`	Prefix match	Yes, with an operator class for `text_pattern_ops` or the C collation
`like '%abc'`	Suffix match	No
`ilike`	Case-insensitive	Only with a trigram index
`~`	Regex, case-sensitive	No, unless anchored and a matching expression index exists
`~*`	Regex, case-insensitive	No
`similar to`	SQL-standard regex	Avoid - the PostgreSQL `~` operator is clearer

A leading wildcard cannot use a B-tree index. When ilike '%term%' is a core query, either add a trigram index or move to full-text search - both are real fixes, whereas a faster server is not.

FAQ

Why is ts_rank giving surprising order?

Ranking depends on the weights, the normalisation method and the query. Pass a normalisation flag such as ts_rank(search, q, 32) to divide by rank plus one, and inspect the weights set in the generated column.

Full-text search or Elasticsearch?

Use PostgreSQL full-text search until you need relevance tuning, faceting, or scale beyond one machine. A second system has to be synchronised, and most applications never outgrow what a GIN index delivers.

Joins, subqueries, CTEs and LATERAL Indexes: B-tree, GIN, GiST, partial and expression

Last refreshed 2026-09-18.

Window functions, full-text search and regex