liveBook · Manning

Columns expression

As explained in DuckDB in Action

A COLUMNS expression is a DuckDB SQL expression that dynamically selects columns matching a regular expression or a lambda function and can project, filter, or aggregate them.

A COLUMNS expression dynamically selects columns by regular expression or lambda function. Use it to project matching columns, filter rows across them, or apply an aggregate to every match. section 3.5.1

Useful patterns include:

  • Select validity columns with SELECT COLUMNS('valid.*') FROM prices. section 3.5.1
  • Compute one maximum per matching column with SELECT max(COLUMNS('valid.*')) FROM prices. section 3.5.1
  • Filter rows where every selected validity column falls between two dates; use a lambda when LIKE-style matching is clearer. section 3.5.1

Filter validity ranges

FROM prices WHERE COLUMNS(col -> col LIKE 'valid%') BETWEEN '2020-01-01' AND '2021-01-01'; section 3.5.1

SELECT COLUMNS('valid.*') FROM prices LIMIT 3;

Inspect the matching validity columns before composing filtering or aggregation expressions. section 3.5.1

How DuckDB in Action covers it

How deep it goes
Demonstrates regular-expression and lambda selection across projection, filtering, and aggregation, without deeper implementation details.
Examples
Uses prices.valid_from and prices.valid_until for projection, maximum aggregation, and date-range filtering.
What you can do after
Apply it via DuckDB SQL to query or transform matching columns without writing each column name.
How it connects
Connects regular expressions, lambda functions, SELECT projection, WHERE filtering, and aggregation.
Cover of DuckDB in Action
liveBookmanning.com
Read DuckDB in Action on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of DuckDB in Action. The section links point to the parts of the book it draws on.